您当前的位置： 首页 > 慢生活 > 程序人生 网站首页程序人生

12_约束_外键约束_级联操作

发布时间：2022-07-12 22:24:23编辑：雪饮阅读（）

 这次介绍下SQLyog的架构设计器

上次我们建立了t1表和t2表，并且t2表的select_t1_id字段是一个外键，参考t1表的id字段。
那么”文件”=》“新架构设计器”打开后
将我们左侧区域的t1表和t2表都拖拽到这个新打开的架构设计器中就自动形成了架构的图形化，这样看起来更直观点。

为了使得架构更清晰点，咱们把表设计为如下结构

 
这里我们用一些稍微正规点的数据

这里可以看到我们的商品001和商品002的category_id都是9，也就是对应category表中id为9的数据。
那么我这里想要修改category表中id为9的这条数据的id为91
mysql> update category set id=91 where id=9;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`test`.`goods`, CONSTRAINT `category_id` FOREIGN KEY (`category_id`) REFERENCES `category` (`id`))
看来很抱歉是无法修改了，因为有外键参考了该表的id为9的这条数据id。
但是我修改这个category表的id为9的这条数据的name字段还是可以的
mysql> update category set name="category9" where id=9;
Query OK, 1 row affected (2.28 sec)
Rows matched: 1 Changed: 1 Warnings: 0
 
 
那么如果非要强制修改category表的id则，goods表中的外键字段category_id的约束条件应该添加一个级联更新
ALTER TABLE `test`.`goods` DROP FOREIGN KEY `category_id`;
ALTER TABLE `test`.`goods` ADD CONSTRAINT `category_id` FOREIGN KEY (`category_id`) REFERENCES `test`.`category`(`id`) ON UPDATE CASCADE;
 
此时我们再次来执行下更category表中的id为9的修改为id为91
mysql> update category set id=91 where id=9;
Query OK, 1 row affected (2.30 sec)
Rows matched: 1 Changed: 1 Warnings: 0
 
mysql> select * from goods;
+----+-------------+------------+
| id | category_id | goods_name |
+----+-------------+------------+
| 1 |          91 | 商品001    |
| 2 |          91 | 商品002    |
+----+-------------+------------+
2 rows in set (0.00 sec)
可见修改后则参考该category表中id字段的goods表的category_id字段也就对应更新了。
（这种效果个人觉得不是很好）
级联删除
了解了级联更新，则级联删除类同
ALTER TABLE `test`.`goods` DROP FOREIGN KEY `category_id`;
ALTER TABLE `test`.`goods` ADD CONSTRAINT `category_id` FOREIGN KEY (`category_id`) REFERENCES `test`.`category`(`id`) ON UPDATE CASCADE ON DELETE CASCADE;
同样的级联删除时，被参考表的对应id对应的数据被删除后，则参考表中的哪些参考了被参考表中已被删除的这些数据的参考键对应的参考表中的这些符合条件的外键的数据条目也一并被删除了。
mysql> delete from category where id=91;
Query OK, 1 row affected (2.31 sec)
 
mysql> select * from goods;
Empty set (0.00 sec)
 
 
扩展阅读：
1.首先明确一个概念，假如表A（id，name，foreign_id），表B（foreign_id,name），我们说表A参考了表B的主键作为其外键使用，所以B表示父表，A表是子表
2.删除和更新有四种设置方式
（1）cascade：级联，当父表更新、删除，子表会同步更新和删除
（2）set null：置空，当父表更新、删除的时候，字表会把外键字段变为null，所以这个时候设计表的时候该字段要允许为null，否则会出错
（3）restrict：父表在删除和更新记录的时候，要在子表中检查是否有有关该父表要更新和删除的记录，如果有，则不允许删除个更改
（4）no action：和restrict一样