mysql设置外键约束on delete cascade on update cascade

[size=small]mysql设置外键约束on delete cascade on update cascade


摘要: 当删除父节点时，由数据库来帮助删除子节点，这样就不用我们显示地写代码先删子节点，再删父节点了。

外键约束创建举例：
ALTER TABLE a
ADD CONSTRAINT `FK_Reference_1`
FOREIGN KEY (`parent_id` )
REFERENCES parent_table (`parent_id` )
ON DELETE CASCADE
ON UPDATE RESTRICT;


1）先看On Delete属性
可能取值为：No Action, Cascade,Set Null, Restrict属性。
当取值为No Action或者Restrict时，则当在父表（即外键的来源表）中删除对应记录时，首先检查该记录是否有对应外键，如果有则不允许删除。

当取值为Cascade时，则当在父表（即外键的来源表）中删除对应记录时，首先检查该记录是否有对应外键，如果有则也删除外键在子表（即包含外键的表）中的记录。

当取值为Set Null时，则当在父表（即外键的来源表）中删除对应记录时，首先检查该记录是否有对应外键，如果有则设置子表中该外键值为null（不过这就要求该外键允许取null）。


2）看看on update属性：
当取值为No Action或者Restrict时，则当在父表（即外键的来源表）中更新对应记录时，首先检查该记录是否有对应外键，如果有则不允许更新。

当取值为Cascade时，则当在父表（即外键的来源表）中更新对应记录时，首先检查该记录是否有对应外键，如果有则也更新外键在子表（即包含外键的表）中的记录。

当取值为Set Null时，则当在父表（即外键的来源表）中更新对应记录时，首先检查该记录是否有对应外键，如果有则设置子表中该外键值为null（不过这就要求该外键允许取null）。
 
比如我有这样两张表：
tbluser用户表：
CREATE TABLE tbluser (
UserID varchar(50) NOT NULL primary key,
UserName varchar(40) NOT NULL,
UserMail varchar(50) NOT NULL,
UserPassword varchar(50) NOT NULL,
UserType tinyint(3) unsigned DEFAULT '0',
UserCreated datetime DEFAULT '0000-00-00 00:00:00'
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

tblfile文件（用户的文件）表：
CREATE TABLE tblfile (
FileID int(10) unsigned NOT NULL AUTO_INCREMENT,
FileOwner varchar(50) DEFAULT NULL,
FileName varchar(200) NOT NULL,
FilePath varchar(200) NOT NULL,
FileType varchar(10) NOT NULL,
FileSubject varchar(100) NOT NULL,
FileCreated datetime DEFAULT '0000-00-00 00:00:00',
PRIMARY KEY (FileID)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

create index ind_FileOwner on tblfile(FileOwner);
ALTER TABLE `tblfile` ADD CONSTRAINT `FK_tblfile_1` FOREIGN KEY (`FileOwner`) REFERENCES `tbluser` (`UserID`) ON DELETE SET NULL ON UPDATE CASCADE;


上面可以看出FileOwner是文件表的外键，引用的是用户表的UserID。且这里外键约束设置为"ON DELETE SET NULL ON UPDATE CASCADE " 
现在用户表有记录(省略其他无关的字段）：

mysql> select * from tbluser;
+--------+----------+------------------+--------------+----------+---------------------+
| UserID | UserName | UserMail | UserPassword | UserType | UserCreated |
+--------+----------+------------------+--------------+----------+---------------------+
| 1 | tina | 27145@qq.com | tina | 0 | 0000-00-00 00:00:00 |
| 2 | bobo | 6964@qq.com | bobo | 1 | 2016-12-27 16:07:51 |
| 3 | lio | 1649797en@qq.com | jiou | 2 | 2016-11-21 16:08:31 |
| 4 | kaka | 46416496@163.com | jiujiu | 0 | 2016-11-06 16:09:03 |
+--------+----------+------------------+--------------+----------+---------------------+
4 rows in set (0.00 sec)

mysql> select * from tblfile;
+--------+-----------+----------+--------------+----------+-------------+---------------------+
| FileID | FileOwner | FileName | FilePath | FileType | FileSubject | FileCreated |
+--------+-----------+----------+--------------+----------+-------------+---------------------+
| 1 | 1 | test | /tmp/a.txt | 1 | en | 2016-12-04 16:09:39 |
| 2 | 4 | jiujiu | /tmp/jiu.txt | 2 | jieng | 0000-00-00 00:00:00 |
| 3 | 4 | enogeh | /tmp/3hoge | 2 | eg | 0000-00-00 00:00:00 |
| 4 | 2 | egoe | /tmp/ghoeh | 3 | eighha | 2016-12-06 16:10:42 |
+--------+-----------+----------+--------------+----------+-------------+---------------------+
4 rows in set (0.00 sec)


我们来删除父表数据：
mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)

mysql> savepoint p1;
Query OK, 0 rows affected (0.00 sec)

mysql> delete from tbluser where UserId=2; 父表删除，子表相关联的字段就被set null
Query OK, 1 row affected (0.00 sec)

mysql> select * from tblfile;
+--------+-----------+----------+--------------+----------+-------------+---------------------+
| FileID | FileOwner | FileName | FilePath | FileType | FileSubject | FileCreated |
+--------+-----------+----------+--------------+----------+-------------+---------------------+
| 1 | 1 | test | /tmp/a.txt | 1 | en | 2016-12-04 16:09:39 |
| 2 | 4 | jiujiu | /tmp/jiu.txt | 2 | jieng | 0000-00-00 00:00:00 |
| 3 | 4 | enogeh | /tmp/3hoge | 2 | eg | 0000-00-00 00:00:00 |
| 4 | NULL | egoe | /tmp/ghoeh | 3 | eighha | 2016-12-06 16:10:42 |
+--------+-----------+----------+--------------+----------+-------------+---------------------+
4 rows in set (0.00 sec)


我们来更新父表数据：
mysql> update tbluser set UserId=5 where UserId=4; ---父表更新，子表更新
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> select * from tblfile;
+--------+-----------+----------+--------------+----------+-------------+---------------------+
| FileID | FileOwner | FileName | FilePath | FileType | FileSubject | FileCreated |
+--------+-----------+----------+--------------+----------+-------------+---------------------+
| 1 | 1 | test | /tmp/a.txt | 1 | en | 2016-12-04 16:09:39 |
| 2 | 5 | jiujiu | /tmp/jiu.txt | 2 | jieng | 0000-00-00 00:00:00 |
| 3 | 5 | enogeh | /tmp/3hoge | 2 | eg | 0000-00-00 00:00:00 |
| 4 | NULL | egoe | /tmp/ghoeh | 3 | eighha | 2016-12-06 16:10:42 |
+--------+-----------+----------+--------------+----------+-------------+---------------------+
4 rows in set (0.00 sec)

mysql> rollback to p1; ---回滚为后面测试做准备。
Query OK, 0 rows affected (0.01 sec)

mysql> select * from tblfile;
+--------+-----------+----------+--------------+----------+-------------+---------------------+
| FileID | FileOwner | FileName | FilePath | FileType | FileSubject | FileCreated |
+--------+-----------+----------+--------------+----------+-------------+---------------------+
| 1 | 1 | test | /tmp/a.txt | 1 | en | 2016-12-04 16:09:39 |
| 2 | 4 | jiujiu | /tmp/jiu.txt | 2 | jieng | 0000-00-00 00:00:00 |
| 3 | 4 | enogeh | /tmp/3hoge | 2 | eg | 0000-00-00 00:00:00 |
| 4 | 2 | egoe | /tmp/ghoeh | 3 | eighha | 2016-12-06 16:10:42 |
+--------+-----------+----------+--------------+----------+-------------+---------------------+
4 rows in set (0.00 sec)


删除之前的外键约束，改一下规则。
ALTER TABLE `tblfile` DROP FOREIGN KEY `FK_tblfile_1`;
ALTER TABLE `tblfile` ADD CONSTRAINT `FK_tblfile_1` FOREIGN KEY (`FileOwner`) REFERENCES `tbluser` (`UserID`) ON DELETE RESTRICT ON UPDATE NO ACTION;

再来一次上面的操作：
mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)

mysql> savepoint p1;
Query OK, 0 rows affected (0.00 sec)

mysql> delete from tbluser where UserId=2; --父表删除和更新都被限制，因为子表有这个用户的信息。
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`tina`.`tblfile`, CONSTRAINT `FK_tblfile_1`
FOREIGN KEY (`FileOwner`) REFERENCES `tbluser` (`UserID`) ON UPDATE NO ACTION)
mysql> update tbluser set UserId=5 where UserId=4;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`tina`.`tblfile`, CONSTRAINT `FK_tblfile_1`
FOREIGN KEY (`FileOwner`) REFERENCES `tbluser` (`UserID`) ON UPDATE NO ACTION)

no action和RESTRICT 是一样的，就是有记录就不允许操作。


再改一下规则：
ALTER TABLE `tblfile` DROP FOREIGN KEY `FK_tblfile_1`;
ALTER TABLE `tblfile` ADD CONSTRAINT `FK_tblfile_1` FOREIGN KEY (`FileOwner`) REFERENCES `tbluser` (`UserID`) ON DELETE CASCADE ON UPDATE CASCADE;

mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)

mysql> savepoint p1;
Query OK, 0 rows affected (0.00 sec)

mysql> delete from tbluser where UserId=2;
Query OK, 1 row affected (0.00 sec)

mysql> select * from tblfile;
+--------+-----------+----------+--------------+----------+-------------+---------------------+
| FileID | FileOwner | FileName | FilePath | FileType | FileSubject | FileCreated |
+--------+-----------+----------+--------------+----------+-------------+---------------------+
| 1 | 1 | test | /tmp/a.txt | 1 | en | 2016-12-04 16:09:39 |
| 2 | 4 | jiujiu | /tmp/jiu.txt | 2 | jieng | 0000-00-00 00:00:00 |
| 3 | 4 | enogeh | /tmp/3hoge | 2 | eg | 0000-00-00 00:00:00 |
+--------+-----------+----------+--------------+----------+-------------+---------------------+
3 rows in set (0.00 sec)

mysql> update tbluser set UserId=5 where UserId=4; --父表中删除和更新，子表就跟着删除和更新。
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> select * from tblfile;
+--------+-----------+----------+--------------+----------+-------------+---------------------+
| FileID | FileOwner | FileName | FilePath | FileType | FileSubject | FileCreated |
+--------+-----------+----------+--------------+----------+-------------+---------------------+
| 1 | 1 | test | /tmp/a.txt | 1 | en | 2016-12-04 16:09:39 |
| 2 | 5 | jiujiu | /tmp/jiu.txt | 2 | jieng | 0000-00-00 00:00:00 |
| 3 | 5 | enogeh | /tmp/3hoge | 2 | eg | 0000-00-00 00:00:00 |
+--------+-----------+----------+--------------+----------+-------------+---------------------+
3 rows in set (0.00 sec)

mysql> rollback to p1;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from tblfile;
+--------+-----------+----------+--------------+----------+-------------+---------------------+
| FileID | FileOwner | FileName | FilePath | FileType | FileSubject | FileCreated |
+--------+-----------+----------+--------------+----------+-------------+---------------------+
| 1 | 1 | test | /tmp/a.txt | 1 | en | 2016-12-04 16:09:39 |
| 2 | 4 | jiujiu | /tmp/jiu.txt | 2 | jieng | 0000-00-00 00:00:00 |
| 3 | 4 | enogeh | /tmp/3hoge | 2 | eg | 0000-00-00 00:00:00 |
| 4 | 2 | egoe | /tmp/ghoeh | 3 | eighha | 2016-12-06 16:10:42 |
+--------+-----------+----------+--------------+----------+-------------+---------------------+
4 rows in set (0.00 sec)


外键的使用对于减少数据库冗余性，以及保证数据完整性和一致性有很大作用。
另外注意，如果两张表之间存在外键关系，则MySQL不能直接删除表(Drop Table)，而应该先删除外键，之后才可以删除。
[/size]