MySQL - 外键使用详解

外键字段必须先是一个索引，否则将会先创建索引，然后才能创建外键。

如果外键字段为混合键值其中一个，需要为该字段建立单独索引。删除的时候必须先删除外键，才能删除索引。

子表中外键字段数据要么为null，要么为父表中引用字段数据！

【1】创建表的时候增加外键

此时创建的索引使用默认名字；创建的外键为表默认名字。

create table my_foreign1(
id int PRIMARY KEY auto_increment,
name varchar(20) default null COMMENT '名字',
age int,
p_id INT,
FOREIGN KEY (p_id) REFERENCES p_user_2(id)  
)CHARSET utf8;

-- FOREIGN KEY fk_id(p_id) REFERENCES p_user_2(id) 
--将指定索引名字为fk_id，但是不能改变外键名字。

---

查看表结构

CREATE TABLE `my_foreign1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) DEFAULT NULL COMMENT '名字',
  `age` int(11) DEFAULT NULL,
  `p_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `p_id` (`p_id`),// 先添加了索引，再创建外键
  CONSTRAINT `my_foreign1_ibfk_1` FOREIGN KEY (`p_id`) REFERENCES `p_user_2` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

---

创建表时创建外键并指定索引名字和外键名字

create table my_foreign1(
id int PRIMARY KEY auto_increment,
name varchar(20) default null COMMENT '名字',
age int,
p_id INT,
CONSTRAINT fk_pid FOREIGN KEY (p_id) REFERENCES p_user_2(id)  
)CHARSET utf8;

-- 索引和外键名字都为 fk_pid;

查看表结构

CREATE TABLE `my_foreign1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) DEFAULT NULL COMMENT '名字',
  `age` int(11) DEFAULT NULL,
  `p_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_pid` (`p_id`),
  CONSTRAINT `fk_pid` FOREIGN KEY (`p_id`) REFERENCES `p_user_2` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

---

【2】创建表后为表增加外键

语法格式如下：

alter table [table_name] 
add [constraint] [constraint_name] 
[unique| primary key|foreign key] 
([column_name])

如果外键字段没有索引，会先添加索引，然后创建外键；

ALTER TABLE t_class 
add CONSTRAINT fk_teacher_id
FOREIGN KEY (t_id) REFERENCES t_teacher(t_id)

---

【3】为表删除外键

• 先删除外键，后删除索引；
• 不能先删除索引，因为有外键约束；

ALTER TABLE t_class 
drop FOREIGN KEY fk_teacher_id(外键名，非字段名);

-- 根据外键名删除外键

ALTER TABLE t_class 
drop index fk_teacher_id;
-- 删除对应索引

---

【4】创建外键的几个条件

• 父表和子表引擎一致，否则报错；

• 保证表的存储引擎为InnoDB，否则虽然不报错但是无约束（只有index）；

  实际上，如果两个表都是MyISAM 引擎的，错误根本不会发生，但也不会产生外键。

• 两个字段数据类型一致，数据显示长度可以不同，另外需注意有符号无符号，必须一致；

• 两个字段都添加了独立索引，如果子表外键字段无索引，那么在创建外键的时候会自动先添加索引；如果父表字段无索引，将报错；

• 字段是否允许为空，在更新或删除时候对外键字段操作有关；如外键创建时候 on delete set null on udpate cascade。但是子表外键字段不允许为空， 矛盾，创建不成功。

• 字段的字符集和校对集(外键类型为字符的时候)；

• 外键名字不能重复，是针对数据库而不是表；

• 子表外键字段 - 数据 为父表引用字段子集；

• 字段可能为混合键值中一个，没有自己独立索引。外键字段必须有自己独立索引。

---

【5】外键的作用

外键默认的作用有两点：一个对父表，一个对子表(外键字段所在的表)。

对子表约束：

子表数据在进行写操作(增和改)的时候，如果对应的外键字段在父表找不到对应的匹配，那么操作会失败。插入的时候外键字段值要么为null，要么为父表中字段。

INSERT into my_foreign1 values(null,'嘉嘉',12,40)

插入的40为外键字段数据，在父表中对应的被引用字段无此数据值。

---

对父表约束：

父表数据进行写操作(删和改：都必须涉及到字段本身)，如果对应的字段在子表中已经被数据所引用，那么就不允许操作。

INSERT into my_foreign1 values(null,'皇甫',18,2)
INSERT into my_foreign1 values(null,'嘉嘉',12,4)

2，4分别为父表里面被引用的字段数据。现在尝试更改2：

---

【6】外键约束模式

外键约束有三种约束模式：都是针对父表； 对应MySQL有四种约束：RESTRICT，NO ACTION , CASCADE , SET NULL。

① District

严格模式(默认的)，父表不能删除或者更新一个已经被子表引用的记录数据(外键对应的父表被引用的字段，其他字段可以更改)。

即，当父表字段数据已经被子表引用时，不能再删除或者更新父表被引用的字段数据。

示例如下：

ALTER TABLE my_foreign1 add CONSTRAINT fk_id
FOREIGN KEY (p_id) REFERENCES  p_user_2(id) 
ON DELETE NO ACTION ON UPDATE NO ACTION;

-- 或者
ON DELETE RESTRICT ON UPDATE RESTRICT;

---

② Cascade

级联模式；针对父表的操作，对应子表关联的数据也跟着被操作。

• 更新父表被引用的字段，如果该数据被子表外键使用，则子表外键随之更新；
• 删除父表被引用的字段记录，如果该数据被子表外键使用，子表对应外键所属记录随之删除。

示例如下：

ALTER TABLE my_foreign1 add CONSTRAINT fk_id
FOREIGN KEY (p_id) REFERENCES  p_user_2(id) 
ON DELETE CASCADE ON UPDATE CASCADE;

---

③Set null

置空模式，父表字段操作之后，如果该数据被子表外键使用，子表对应的外键字段被置空。

外键置空的前提是对应字段允许为空，否则外键创建不成功。

示例如下：

ALTER TABLE my_foreign1 add CONSTRAINT fk_id
FOREIGN KEY (p_id) REFERENCES  p_user_2(id) 
ON DELETE SET NULL ON UPDATE SET NULL;

通常一个合理的做法(约束模式)是：删除的时候子表置空，更新的时候子表级联：

ON DELETE SET NULL ON UPDATE CASCADE;

---

【7】创建外键出现的错误

① errno: 150错误：

这种情况下，参考创建外键的几个条件，一一排查。

② error：121错误：

这种情况下显然是你的外键名字重复。MySQL判断外键名字基础单位是数据库，而不是表。

用如下语句查看数据库已经存在的外键与所属表：

SELECT
    constraint_name,
    table_name
FROM
    information_schema.table_constraints
WHERE
    constraint_type = 'FOREIGN KEY'
AND table_schema = DATABASE()
ORDER BY
    constraint_name;