MySQL外键约束
什么是外检约束
外键其实很好理解，简单的说就是两张表建立一个连接关系。这里我们那主表A和副表B举例，我A表中有用户信息，B表中有用户订单信息。要是数据完整对应起来，肯定是需要把两张表关联起来，我们因此会在B表中村一个A表的字段，常见的我们存的是A表的主键ID外键。
外检约束要求
.MySQL的数据表存储引擎必须为Innodb。 .主表和副表关联的字段数据类型的一致。 .字段不能设置为NULL。 .主表中的字段需为主键。
外键约束的作用
保证数据的完整性和一致性.
创建语法
[CONSTRAINT [symbol]] FOREIGN KEY
[index_name] (index_col_name, ...)
REFERENCES tbl_name (index_col_name,...)
[ON DELETE reference_option]
[ON UPDATE reference_option]
reference_option:
RESTRICT | CASCADE | SET NULL | NO ACTION
1. CASCADE: 从父表中删除或更新对应的行，同时自动的删除或更新自表中匹配的行。ON DELETE CANSCADE和ON UPDATE CANSCADE都被InnoDB所支持。
2. SET NULL: 从父表中删除或更新对应的行，同时将子表中的外键列设为空。注意，这些在外键列没有被设为NOT NULL时才有效。ON DELETE SET NULL和ON UPDATE SET SET NULL都被InnoDB所支持。
3. NO ACTION: InnoDB拒绝删除或者更新父表。
4. RESTRICT: 拒绝删除或者更新父表。指定RESTRICT（或者NO ACTION）和忽略ON DELETE或者ON UPDATE选项的效果是一样的。
5. SET DEFAULT: InnoDB目前不支持。
实战演示
// 主表
mysql> create table userInfo(
-> id int(4) not null primary key auto_increment,
-> nick_name varchar(10) not null,
-> age int(3) default 1)engine=innodb default charset=utf8;
Query OK, 0 rows affected (0.07 sec)
insert into userInfo(`nick_name`, `age`) value ('user001',12),('user002',13);
// 从表
mysql> create table orders(
-> id int(11) not null primary key auto_increment,
-> u_id int(4) not null,
-> number varchar(32) not null)engine=innodb default charset=utf8; Query OK, 0 rows affected (0.03 sec)
insert into orders(`u_id`,`number`) value(1,'1234567890'),(2,'0987654321');
.CASCADE情况
alter table orders add constraint foreign key `f_uid` (`u_id`) references userInfo(`id`) on delete cascade on update cascade;
// 执行删除主表数据
mysql> select * from userInfo;
+----+-----------+------+
| id | nick_name | age |
+----+-----------+------+
| 1 | user001 | 12 |
| 2 | user002 | 13 |
+----+-----------+------+
2 rows in set (0.01 sec)
mysql> select * from orders;
+----+------+------------+
| id | u_id | number |
+----+------+------------+
| 1 | 1 | 1234567890 |
| 2 | 2 | 0987654321 |
+----+------+------------+
2 rows in set (0.00 sec)
mysql> delete from userInfo where id=2;
Query OK, 1 row affected (0.01 sec)
mysql> select * from userInfo;
+----+-----------+------+
| id | nick_name | age |
+----+-----------+------+
| 1 | user001 | 12 |
+----+-----------+------+
1 row in set (0.01 sec)
mysql> select * from orders;
+----+------+------------+
| id | u_id | number |
+----+------+------------+
| 1 | 1 | 1234567890 |
+----+------+------------+
1 row in set (0.00 sec)
结论：从上面可以看出当情况为CASCADE的情况时，这时候只要删除主表，从表相互关联的数据会自动删除。同理推出update的时候也会自动更新从表中的数据。
. NOACTION情况
alter table orders add foreign key `f_uid` (`u_id`) references userInfo(`id`) on delete no action on update no action;
mysql> select * from userInfo;
+----+-----------+------+
| id | nick_name | age |
+----+-----------+------+
| 1 | user001 | 12 |
| 2 | user002 | 13 |
+----+-----------+------+
2 rows in set (0.00 sec)
mysql> select * from orders;
+----+------+------------+
| id | u_id | number |
+----+------+------------+
| 1 | 1 | 1234567890 |
| 2 | 2 | 0987654321 |
+----+------+------------+
2 rows in set (0.00 sec)
mysql> delete from userInfo where id =1;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`test`.`orders`, CONSTRAINT `orders_ibfk_1` FOREIGN KEY (`u_id`) REFERENCES `userInfo` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION)
mysql> delete from orders where id =1;
Query OK, 1 row affected (0.01 sec)
结论:当我们进行删除操作的时候，会提示无法删除或者更新主表。
.RESTRICT
mysql> alter table orders add foreign key `f_uid` (`u_id`) references userInfo(`id`) ON DELETE RESTRICT ON UPDATE RESTRICT;
Query OK, 2 rows affected (0.11 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> select * from orders;
+----+------+------------+
| id | u_id | number |
+----+------+------------+
| 1 | 1 | 1234567890 |
| 2 | 2 | 0987654321 |
+----+------+------------+
2 rows in set (0.00 sec)
mysql> select * from userInfo;
+----+-----------+------+
| id | nick_name | age |
+----+-----------+------+
| 1 | user001 | 12 |
| 2 | user002 | 13 |
+----+-----------+------+
2 rows in set (0.00 sec)
mysql> delete from userInfo where id =1;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`test`.`orders`, CONSTRAINT `orders_ibfk_1` FOREIGN KEY (`u_id`) REFERENCES `userInfo` (`id`))
mysql> delete from orders where id =1;
Query OK, 1 row affected (0.01 sec)
总结:这种情况忽略ON DELETE、ON UPDATE和NO ACTION选项的效果是一样的。
.SET DEFAULT情况
mysql> alter table orders add foreign key `f_uid` (`u_id`) references userInfo(`id`) ON DELETE set default ON UPDATE set default;
ERROR 1215 (HY000): Cannot add foreign key constraint
结论:Innodb存储引擎暂不支持。
.SET NULL情况
mysql> alter table orders add foreign key `f_uid` (`u_id`) references userInfo(`id`) ON DELETE set null ON UPDATE set null;
ERROR 1215 (HY000): Cannot add foreign key constraint
结论:Innodb存储引擎暂不支持。
本文参与 腾讯云自媒体分享计划，分享自微信公众号。
原始发表：2019-07-31，如有侵权请联系 cloudcommunity@tencent.com 删除
评论
登录后参与评论
推荐阅读
相关产品与服务
对象存储
对象存储（Cloud Object Storage，COS）是由腾讯云推出的无目录层次结构、无数据格式限制，可容纳海量数据且支持 HTTP/HTTPS 协议访问的分布式存储服务。腾讯云 COS 的存储桶空间无容量上限，无需分区管理，适用于 CDN 数据分发、数据万象处理或大数据计算与分析的数据湖等多种场景。