「MySQL 外键约束」

  CREATED BY JENKINSBOT

################# 准备

更新日期:2020年09月11日
@IGNORECHANGE
  create table t_group (
   id int not null,
   name varchar(30),
   primary key (id)
);
并插入两条记录
  insert into t_group values (1, ‘Group1’);
insert into t_group values (2, ‘Group2’);

################ 演示

(三种外键约束模式)

1、级联(cascade)方式:(从表中的列也会删除)

  级联方式
  create table t_user (
   d int not null,
   name varchar(30),
   groupid int,
   primary key (id),
   foreign key (groupid) references t_group(id) on delete cascade on update cascade
  );
  参照完整性测试:(从表中的外键必须在主表中存在)
  insert into t_user values (1, ‘qianxin’, 1); #可以插入
  insert into t_user values (2, ‘yiyu’, 2); #可以插入
insert into t_user values (3, ‘dai’, 3); #错误,无法插入,外键3不存在,与参照完整性约束不符

  约束方式测试
  insert into t_user values (1, ‘qianxin’, 1);
  insert into t_user values (2, ‘yiyu’, 2);
  insert into t_user values (3, ‘dai’, 2);
  delete from t_group where id=2; #导致t_user中的2、3记录级联删除
  update t_group set id=2 where id=1; #导致t_user中的1记录的groupid级联修改为2

2、置空(set null)方式(主表中的记录被删除,从表则会被设置为null)

  置空方式
  create table t_user (
   id int not null,
   name varchar(30),
   groupid int,
   primary key (id),
   foreign key (groupid) references t_group(id) on delete set null on update set null
  );
参照完整性测试
insert into t_user values (1, ‘qianxin’, 1); #可以插入
  insert into t_user values (2, ‘yiyu’, 2); #可以插入
  insert into t_user values (3, ‘dai’, 3); #错误,无法插入,外键3不存在,与参照完整性约束不符
  约束方式测试
  insert into t_user values (1, ‘qianxin’, 1);
  insert into t_user values (2, ‘yiyu’, 2);
  insert into t_user values (3, ‘dai’, 2);
  delete from t_group where id=2; #导致t_user中的2、3记录的groupid被设置为NULL
  update t_group set id=2 where id=1; #导致t_user中的1记录的groupid被设置为NULL

3、禁止(no action / restrict)方式(默认的)

  create table t_user (
   id int not null,
   name varchar(30),
   groupid int,
   primary key (id),
   foreign key (groupid) references t_group(id) on delete no action on update no action
  );
  参照完整性测试
  insert into t_user values (1, ‘qianxin’, 1); #可以插入
  insert into t_user values (2, ‘yiyu’, 2); #可以插入
  insert into t_user values (3, ‘dai’, 3); #错误,无法插入,外键3不存在,与参照完整性约束不符
  约束方式测试
  insert into t_user values (1, ‘qianxin’, 1);
  insert into t_user values (2, ‘yiyu’, 2);
  insert into t_user values (3, ‘dai’, 2);
  delete from t_group where id=2; #错误,从表中有相关引用,因此主表中无法删除
  update t_group set id=2 where id=1; #错误,从表中有相关引用,因此主表中无法修改