MySQL

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

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

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

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

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;

---

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

语法格式如下：

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

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

ALTER TABLE t_class add CONSTRAINT fk_teacher_idFOREIGN KEY (t_id) REFERENCES t_teacher(t_id)

---

【为表删除外键】

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

ALTER TABLE t_class drop FOREIGN KEY fk_teacher_id(外键名，非字段名);-- 根据外键名删除外键ALTER TABLE t_class drop index fk_teacher_id;-- 删除对应索引

---

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

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

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

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

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

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

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

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

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

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

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

---

【外键的作用】

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

对子表约束：子表数据在进行写操作(增和改)的时候，如果对应的外键字段在父表找不到对应的匹配，那么操作会失败。插入的时候外键字段值要么为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：

---

【外键约束模式】

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

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

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

示例如下：

ALTER TABLE my_foreign1 add CONSTRAINT fk_idFOREIGN 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_idFOREIGN KEY (p_id) REFERENCES  p_user_2(id) ON DELETE CASCADE ON UPDATE CASCADE;

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

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

示例如下：

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

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

ON DELETE SET NULL ON UPDATE CASCADE;

---

【创建外键出现的错误】

① errno: 150错误：

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

② error：121错误：

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

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

SELECT    constraint_name,    table_nameFROM    information_schema.table_constraintsWHERE    constraint_type = 'FOREIGN KEY'AND table_schema = DATABASE()ORDER BY    constraint_name;