外键如何设定置空和级联操作？

置空和级联一般在创建表时进行设置。

关键字

在删除的时候：on delete

在更新的时候：on update

置空：set null

级联：cascade

创建主表

create table if not exists student(
	stu_id int primary key auto_increment comment 'student表主键stu_id',
	name varchar(30) comment '学生姓名name'
	);

1. 插入数据到student表

   insert into student values
       (null, 'Jim'),
       (null, 'Tom'),
       (null, 'Anna'),
       (null, 'Emma');
   

2. 查询student表

   +--------+------+
   | stu_id | name |
   +--------+------+
   |      1 | Jim  |
   |      2 | Tom  |
   |      3 | Anna |
   |      4 | Emma |
   +--------+------+
   

创建子表

create table if not exists eatery_order(
	id int primary key auto_increment comment 'eatery_order主键',
	money decimal(10,4) default '0.0' comment '消费金额',
	stu_id int comment 'eatery_order表外键',
	foreign key (stu_id) references student(stu_id) on delete set null on update cascade
	);

关键语句

foreign key (stu_id) references student(stu_id) on delete set null on update cascade

解释：

• on delete set null：在主表删除记录时，本表外键值置空 null
• on update cascade：在主表更新记录时，本表外键值同步更新

1. 插入数据到eatery_order表

   insert into eatery_order values
       (null, 7, 1),
       (null, 11, 3),
       (null, 11, 3),
       (null, 20, 4),
       (null, 7, 2),
       (null, 7, 4);
   

2. 查询eatery_order表

   +----+---------+--------+
   | id | money   | stu_id |
   +----+---------+--------+
   |  1 |  7.0000 |      1 |
   |  2 | 11.0000 |      3 |
   |  3 | 11.0000 |      3 |
   |  4 | 20.0000 |      4 |
   |  5 |  7.0000 |      2 |
   |  6 |  7.0000 |      4 |
   +----+---------+--------+
   

来感受置空和级联操作的魅力吧

置空

1. 删除student表中的一些记录：

   delete from student where name='Emma';
   

2. 查询eatery_order表

   +----+---------+--------+
   | id | money   | stu_id |
   +----+---------+--------+
   |  1 |  7.0000 |      1 |
   |  2 | 11.0000 |      3 |
   |  3 | 11.0000 |      3 |
   |  4 | 20.0000 |   NULL |
   |  5 |  7.0000 |      2 |
   |  6 |  7.0000 |   NULL |
   +----+---------+--------+
   

   可以看到在eatery_order表中，原本为4的stu_id，现在都变成了null。

级联

1. 更新student表中的一些记录：

   update student set stu_id=456 where stu_id=3;
   

2. 查询eatery_order表

   +----+---------+--------+
   | id | money   | stu_id |
   +----+---------+--------+
   |  1 |  7.0000 |      1 |
   |  2 | 11.0000 |    456 |
   |  3 | 11.0000 |    456 |
   |  4 | 20.0000 |   NULL |
   |  5 |  7.0000 |      2 |
   |  6 |  7.0000 |   NULL |
   +----+---------+--------+
   

   可以看到在eatery_order表中，原本为3的stu_id，现在都变成了456。