LJ你是唯一LT
- 浏览: 238976 次
-
最新评论
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)，而应该先删除外键，之后才可以删除。
摘要: 当删除父节点时，由数据库来帮助删除子节点，这样就不用我们显示地写代码先删子节点，再删父节点了。
外键约束创建举例：
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)，而应该先删除外键，之后才可以删除。
分享到：
发表评论
-
mysql权限管理（实例）
2016-05-10 17:21 1484mysql权限管理实例 本文并没有很详细的介绍对具体的对象授 ... -
mysql简单的碎片清理脚本
2016-05-10 16:52 1462mysql简单的碎片清理脚本 #!/bin/bash date ... -
mysql qpress压缩备份恢复
2016-05-03 16:30 6800说明： 1.前面博客已经介绍过gzip压缩方法，备份正常，但后 ... -
mysql xtrabackup在线搭建主从
2016-04-11 14:59 1888使用xtrabackup进行在线的主从搭建： [root@m ... -
mysql xtrabackup在线备份还原（全备+增备）
2016-04-11 14:47 1007工具安装： [root@mysqlserver var]# t ... -
mysql主库清理数据，从库保留
2016-04-01 15:26 1255因为业务需要，想在mysql主库清理一些数据，但从库想要保留， ... -
oracle，postgresql，mysql一些使用上的区别记录
2015-12-16 11:38 01.限制行数： select * from ta where ... -
数据库调优分享-mysql
2015-12-16 10:02 924数据库调优分享------参考一本mysql资料书 日常的困 ... -
mysql 安装-tina
2015-12-08 17:32 0mysql安装-tina 1、准备安装程序（http://ww ... -
mysqldump 只导入数据或只导结构
2015-12-22 10:36 2682[size=small]mysqldump只导出数据或只导出表 ... -
mysql server has gone away
2015-12-10 09:26 840mysql server has gone away，他的意思 ... -
mysql optimize 清理碎片
2015-12-09 09:26 1181---定期清理脚本 0 1 * * 4 root /root ... -
mysql binlog
2015-12-10 09:26 1321mysqld在每个二进制日志 ... -
mysql远程连接设置
2015-12-10 09:25 973远程连接mysql数据库： 连接上以后，通过这台跳转服务器远 ... -
Last_SQL_Error: Error 'Duplicate entry '1' for key 'PRIMARY''
2015-12-10 09:25 1688[size=small]-实际遇到的问题： Last_SQL ... -
[ERROR] Slave I/O: error connecting to master
2015-12-09 09:26 8076刚配置的MySQL主从，在从机上看到 点击(此处)折叠或打开 ... -
MySQL常用函数
2015-02-05 10:34 504一、字符串类 1、left（str, length） 从左开始 ... -
MySQL触发器简介
2015-02-05 10:33 870一、触发器基本语法 CREATE TRIGGER trigge ... -
MySQL主从切换
2015-02-05 10:32 468环境： 原主库：192.168.10.197 ---新 ... -
MySQL主从搭建
2015-02-05 10:31 755环境简介 master(主）：192.168.12.101 s ...
相关推荐
外键表名>(外键列名) 事件触发限制: on delete和on update , 可设参数cascade(跟随外键改动), restrict(限制外表中的外键改动),set Null(设空值）,set Default（设默认值）,[默认]no action 例如： ...
mysql外键的设置，使用。添加外键的格式: ALTER TABLE yourtablename ADD [CONSTRAINT 外键名] FOREIGN KEY [id] (index_col_name, ...) REFERENCES tbl_name (index_col_name, ...) [ON DELETE {CASCADE | ...
添加级联操作 语法：ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段名称) REFERENCES 主表名称(主表列名称) ON UPDATE CASCADE ON DELETE CASCADE ; 2. 分类： 1. 级联更新：ON UPDATE CASCADE 2...
on delete 级联动作 on update 级联动作 3、级联动作 1、cascade ：删除、更新同步(被参考字段) 2、restrict ：不让主表更新、删除 3、set null ：删除、更新,从表该字段设置为 NULL 4、删除 1、show create...
针对上面创建的两个表， 子表的外键指定是ON DELETE RESTRICT ON UPDATE CASCADE 方式的， 那么在主表删除记录的时候， 如果子表有对应记录， 则不允许删除， 主表在更新记录的时候， 如果子表有对应记录， 则子表...
STATE VARCHAR(10) CHECK(STATE IN('等待','执⾏','完成')), FOREIGN KEY(USERID) REFERENCES USERINFO(USERID) ON DELETE NO ACTION ON UPDATE CASCADE, FOREIGN KEY(BOOKID) REFERENCES BOOK(BOOKID) ON DELETE ...
级联删除：删除主表的数据时，关联的从表数据也删除，则需要在建立外键约束的后面增加on deletecascade 或on delete set null，前者是级联删除，后者是将从表的关联列的值设置为null。 create table student( id int...
on update cascade 同步更新 二、Mysql基本介绍 操作文件夹（库）: 增加一个库：create database db1 charset utf8; 查看所有库: show databases; 查看特定库: show create database db1; 删库跑路: drop database ...
set pause on\off ：设置分屏(设置不分屏) set pause "please put an enter key" 且 set pause on：设置带有提示的分屏 oerr ora 904 ：查看错误 set head off ：去掉表头 set feed off ：去掉表尾 保存在...
2)我想把emp所有的(增，删，改，查/insert,delete,update,select这四个一起可以用all代替) 操作权限赋给leng grant all on emp to leng;(此时登陆用户为scott) 案例2:这时我想把权限收回来怎么办呢, 这时用...
many2one_update11. many2one_delete12. one2many13. one2many_cascade14. one2one15. many2many16.如何删除MYSQL sudo apt-get remove --purge mysql*如何检查是否清除？ dpkg -l | grep mysql删除配置信息sudo ...
在创建表时，经常会创建该表的主键、外键、唯一约束、Check约束等 语法结构 create table 表名( [字段名] [类型] [约束] ……….. CONSTRAINT fk_column FOREIGN KEY(column1,column2,…..column_n) ...