【转】《与MySQL的零距离接触》第三章:约束以及修改数据表 (3-3:MySQL 外键约束的参照操作)
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这个关键词来定义。
下一篇: MySQL数据库基本命令学习(2)