外键

表结构1：

CREATE TABLE `t1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) DEFAULT NULL,
  `createtime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

表结构2：

 CREATE TABLE `t2` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `t_id` int(11) DEFAULT NULL,
  `createtime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `t_id_fk` (`t_id`),
  CONSTRAINT `t_id_fk` FOREIGN KEY (`t_id`) REFERENCES `t1` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

 设置了主键和外键的字段，默认会创建该字段的索引

show index from t2;

 执行结果

+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| t2    |          0 | PRIMARY  |            1 | id          | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| t2    |          1 | t_id_fk  |            1 | t_id        | A         |           0 |     NULL | NULL   | YES  | BTREE      |         |               |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

为t1插入数据

+----+------+---------------------+
| id | name | createtime          |
+----+------+---------------------+
|  1 | a    | 2018-06-27 10:51:36 |
|  2 | b    | 2018-06-27 10:51:36 |
|  3 | c    | 2018-06-27 10:51:36 |
|  4 | d    | 2018-06-27 10:51:36 |
+----+------+---------------------+

为t2插入数据，由于外键约束的存在，t2(t_id)列的数据必须都来自于t1(id)列

insert into t2 (t_id) values (5);

 执行结果

ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`pmx`.`t2`, CONSTRAINT `t_id_fk` FOREIGN KEY (`t_id`) REFERENCES `t1` (`id`))

insert into t2 (t_id) values (3),(2),(4),(3),(1),(4);

 执行结果

+----+------+---------------------+
| id | t_id | createtime          |
+----+------+---------------------+
|  2 |    3 | 2018-06-27 10:55:29 |
|  3 |    2 | 2018-06-27 10:55:29 |
|  4 |    4 | 2018-06-27 10:55:29 |
|  5 |    3 | 2018-06-27 10:55:29 |
|  6 |    1 | 2018-06-27 10:55:29 |
|  7 |    4 | 2018-06-27 10:55:29 |
+----+------+---------------------+

t2.t_id字段的数据必须来自于t1.id，不能出现t1.id中没有的数据。id号从2开始，因为第一次插入5时失败了，但是auto_increment仍然从1变为了2。

t_id字段的更新受外键的约束，取值范围只能来自于t1的id字段值。

t2的删除操作不受外键的约束

update t2 set t_id = 3 where id = 7;
delete from t2 where id = 4;

 执行结果

+----+------+---------------------+
| id | t_id | createtime          |
+----+------+---------------------+
|  2 |    3 | 2018-06-27 10:55:29 |
|  3 |    2 | 2018-06-27 10:55:29 |
|  5 |    3 | 2018-06-27 10:55:29 |
|  6 |    1 | 2018-06-27 10:55:29 |
|  7 |    3 | 2018-06-27 10:55:29 |
+----+------+---------------------+

对主表t1执行删除操作时，如果删除的主键值在子表t2中出现，那么就删除失败

delete from t1 where id = 4;

执行结果

+----+------+---------------------+
| id | name | createtime          |
+----+------+---------------------+
|  1 | a    | 2018-06-27 10:51:36 |
|  2 | b    | 2018-06-27 10:51:36 |
|  3 | c    | 2018-06-27 10:51:36 |
+----+------+---------------------+

删除成功

delete from t1 where id = 3;

执行结果

ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`pmx`.`t2`, CONSTRAINT `t_id_fk` FOREIGN KEY (`t_id`) REFERENCES `t1` (`id`)) 

删除失败

 

来看更新操作

insert into t1 (name) values ('d'); 

 执行结果

+----+------+---------------------+
| id | name | createtime          |
+----+------+---------------------+
|  1 | a    | 2018-06-27 10:51:36 |
|  2 | b    | 2018-06-27 10:51:36 |
|  3 | c    | 2018-06-27 10:51:36 |
|  5 | d    | 2018-06-27 11:06:24 |
+----+------+---------------------+

受auto_increment的特性影响，id变成了5

update t1 set id = 7 where id = 5;

执行结果

+----+------+---------------------+
| id | name | createtime          |
+----+------+---------------------+
|  1 | a    | 2018-06-27 10:51:36 |
|  2 | b    | 2018-06-27 10:51:36 |
|  3 | c    | 2018-06-27 10:51:36 |
|  7 | d    | 2018-06-27 11:06:24 |
+----+------+---------------------+

 更新成功。5和7并未在t2中出现

update t1 set id = 8 where id =1;

执行结果

ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`pmx`.`t2`, CONSTRAINT `t_id_fk` FOREIGN KEY (`t_id`) REFERENCES `t1` (`id`)

更新失败

所以更新操作和删除操作一样，如果操作的值在子表中出现，那么就会操作失败

truncate table t2;

成功清空

truncate table t1;

ERROR 1701 (42000): Cannot truncate a table referenced in a foreign key constraint (`pmx`.`t2`, CONSTRAINT `t_id_fk` FOREIGN KEY (`t_id`) REFERENCES `pmx`.`t1` (`id`))

为了能清空t1,我们只能先删除t2的外键再清空t1

alter table t2 drop foreign key t_id_fk;
alter table t2 drop key t_id_fk;

truncate table t1;

Query OK, 0 rows affected (0.02 sec)

外键定制三种约束模式

　　restrict和no action:父表更新或者删除时，子表有匹配记录，则禁止父表的更新和删除。默认选项

　　cascade：父表更新或者删除时，子表有匹配记录，则父表操作成功，同时更新或删除子表匹配项

　　set null：父表更新或者删除时，子表有匹配记录，则父表操作成功，同时将子表的匹配项设置为null值(前提能设置为null)　

alter table t2 add constraint c_fk foreign key t_id references t1(id) [on {delete | update} { restrict | no action | cascade | set null}];