【转】《与MySQL的零距离接触》第三章：约束以及修改数据表 （3-3：MySQL 外键约束的参照操作）

转载出处：

慕课网：《与MySQL的零距离接触》笔记目录https://zhangjia.tv/682.html

3-3：MySQL 外键约束的参照操作

一.  前言

创建外键的时候，我们可以协商外键约束的参照操作，一共有四个选项：

• CASCADE :从父表删除或更新且自动删除或更新子表中匹配的行

• SET NULL: 从父表删除或更新行，并设置子表中的外键列为NULL。如果使用该选项，必须保证子表列没有指定NOT NULL

• RESTRICT: 拒绝对父表的删除或更新操作。

• NO ACTION: 标准SQL的关键字，在MySQL中与RESTRICT相同

那么这四个操作是指进行了外键约束的创建以后，在更新表的时候，子表是否也进行相应的操作，接下来我们以CASCADE为例讲解。

二.  CASCADE

CASCADE是指从父表删除或更新且自动删除或更新子表中匹配的行，首先我们再创建一个数据表，并设置外键约束的参照操作的为CASCADE：

mysql> CREATE TABLE users1(
    -> id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    -> username VARCHAR(10) NOT NULL,
    -> pid SMALLINT UNSIGNED,
    -> FOREIGN KEY(pid) REFERENCES provinces(id) ON DELETE CASCADE
    -> );
Query OK, 0 rows affected (0.01 sec)

然后查看一下user1表的创建命令：

mysql> SHOW CREATE TABLE users1;
+--------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table  | Create Table                                                                                                                                                                                                                                                                                                                                |
+--------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| users1 | CREATE TABLE `users1` (
  `id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
  `username` varchar(10) NOT NULL,
  `pid` smallint(5) unsigned DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `pid` (`pid`),
  CONSTRAINT `users1_ibfk_1` FOREIGN KEY (`pid`) REFERENCES `provinces` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+--------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

可以看到ON DELETE CASCADE，指的就是从父表删除或更新且自动删除或更新子表中匹配的行，我们来验证一下：

既然要更新相应的行，那么也就代表数据表中必须存在中相应的记录才可以，所以需要在2张表中插入记录，值得注意的是，必须现在父表中插入记录，然后再在子表中插入记录，首先在上一节中的省份表中插入记录并检验是否插入成功：

mysql> INSERT provinces(pname) VALUES('A');
Query OK, 1 row affected (0.01 sec)

mysql> INSERT provinces(pname) VALUES('B');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT provinces(pname) VALUES('C');
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM provinces;
+----+-------+
| id | pname |
+----+-------+
|  1 | A     |
|  2 | B     |
|  3 | C     |
+----+-------+
3 rows in set (0.00 sec)

接下来再在users1表中插入记录：

mysql> INSERT users1(username,pid) VALUES('Tom',3);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT users1(username,pid) VALUES('John',7);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`test`.`users1`, CONSTRAINT `users1_ibfk_1` FOREIGN KEY (`pid`) REFERENCES `provinces` (`id`) ON DELETE CASCADE)
mysql> INSERT users1(username,pid) VALUES('John',1);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT users1(username,pid) VALUES('rose',3);
Query OK, 1 row affected (0.00 sec)

值得注意的是，当我们插入第二条记录的时候，因为pid没有7所以没有插入成功，但是编号也会自动递增1，所以我们查看一下users1记录，会发现id是1,3,4，而不是1,2,3

mysql> SELECT * FROM users1;
+----+----------+------+
| id | username | pid  |
+----+----------+------+
|  1 | Tom      |    3 |
|  3 | John     |    1 |
|  4 | rose     |    3 |
+----+----------+------+
3 rows in set (0.00 sec)

现在我们在省份表中删除id为3的记录：

mysql> DELETE FROM provinces WHERE id = 3;
Query OK, 1 row affected (0.01 sec)

可以看到删除成功，一条被影响， 然后我们查看一下省份表:

mysql> SELECT * FROM provinces;
+----+-------+
| id | pname |
+----+-------+
|  1 | A     |
|  2 | B     |
+----+-------+
2 rows in set (0.00 sec)

可以看到id为3的记录已经被删除，再查看一下users1表，发现所有pid为3的记录也已经被删除，CASCADE生效：

mysql> SELECT * FROM users1;
+----+----------+------+
| id | username | pid  |
+----+----------+------+
|  3 | John     |    1 |
+----+----------+------+
1 row in set (0.00 sec)

另外，在本节教程的最后关于实际开发中的注意事项，由Java家洼整理如下：

在外键约束当中，在实际的开发过程中，我们很少使用物理的外键约束，很多都去使用逻辑的外键约束

因为物理的外键约束只有INNODB这种引擎才会支持，像我们另外的一种引擎MYISAM的引擎则不支持

反过来说，如果我想创建的数据表，假设存储引擎为MYISAM，而且又想使用外键约束的话，其实是不可能实现的

所以说，我们在实际的项目开发中，我们不去定义物理的外键

所谓的逻辑外键指的是就是我们在定义两张表的结构的时候，我们是按照存在的某种结构的方式去定义，但是不去使用FOREIGN KEY这个关键词来定义。