【金沙官网线上】转载--外键三种作用方式

如果表A的主关键字是表B中的字段,则该字段称为表B的外键,表A称为主表,表B称为从表。外键是用来实现参照完整性的,不同的外键约束方式将可以使两张表紧密的结合起来,特别是修改或者删除的级联操作将使得日常的维护工作更加轻松。这里以MySQL为例,总结一下3种外键约束方式的区别和联系。 

     这里以用户表和用户组表为例,这是一个典型的多对一关系,多个用户对应于一个用户组。 
     首先创建用户组表: 

Sql代码 

  1. create table t_金沙官网线上,group (   
  2.     id int not null,   
  3.     name varchar(30),   
  4.     primary key (id)   
  5. );  

    create table t_group (id int not null,name varchar(30),primary key (id));

并插入两条记录: 

Sql代码 

  1. insert into t_group values (1, 'Group1');   
  2. insert into t_group values (2, 'Group2');  

    insert into t_group values (1, 'Group1');insert into t_group values (2, 'Group2');

下面创建用户表,分别以不同的约束方式创建外键引用关系: 
1、级联(cascade)方式 :当你更新或删除主键表时,那么外键表也会跟随一起更新或删除。

Sql代码 

  1. create table t_user (   
  2.     id int not null,   
  3.     name varchar(30),   
  4.     groupid int,   
  5.     primary key (id),   
  6.     foreign key (groupid) references t_group(id) on delete cascade on update cascade  
  7. );  

    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 cascade on update cascade);

参照完整性测试 

Sql代码 

  1. insert into t_user values (1, 'qianxin', 1); --可以插入   
  2. insert into t_user values (2, 'yiyu', 2);    --可以插入   
  3. 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', 3); --错误,无法插入,用户组3不存在,与参照完整性约束不符

约束方式测试 

Sql代码 

  1. insert into t_user values (1, 'qianxin', 1);   
  2. insert into t_user values (2, 'yiyu', 2);   
  3. insert into t_user values (3, 'dai', 2);   
  4. delete from t_group where id=2;              --导致t_user中的2、3记录级联删除   
  5. update t_group set id=2 where id=1;          --导致t_user中的1记录的groupid级联修改为2  

    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。

Sql代码 

  1. 置空方式   
  2. create table t_user (   
  3.     id int not null,   
  4.     name varchar(30),   
  5.     groupid int,   
  6.     primary key (id),   
  7.     foreign key (groupid) references t_group(id) on delete set null on update set null  
  8. );   
  9.   
  10. 参照完整性测试   
  11.   
  12. insert into t_user values (1, 'qianxin', 1); --可以插入   
  13. insert into t_user values (2, 'yiyu', 2);    --可以插入   
  14. insert into t_user values (3, 'dai', 3);     --错误,无法插入,用户组3不存在,与参照完整性约束不符   
  15.   
  16. 约束方式测试   
  17. insert into t_user values (1, 'qianxin', 1);   
  18. insert into t_user values (2, 'yiyu', 2);   
  19. insert into t_user values (3, 'dai', 2);   
  20. delete from t_group where id=2;              --导致t_user中的2、3记录的groupid被设置为NULL   
  21. update t_group set id=2 where id=1;          --导致t_user中的1记录的groupid被设置为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被设置为NULLupdate t_group set id=2 where id=1; --导致t_user中的1记录的groupid被设置为NULL

3、禁止(no action / restrict)方式 ,从表中有相关外键是,因此主表中无法进行修改和删除操作。  

Sql代码 

  1. 禁止方式   
  2. create table t_user (   
  3.     id int not null,   
  4.     name varchar(30),   
  5.     groupid int,   
  6.     primary key (id),   
  7.     foreign key (groupid) references t_group(id) on delete no action on update no action  
  8. );   
  9.   
  10. 参照完整性测试   
  11. insert into t_user values (1, 'qianxin', 1); --可以插入   
  12. insert into t_user values (2, 'yiyu', 2);    --可以插入   
  13. insert into t_user values (3, 'dai', 3);     --错误,无法插入,用户组3不存在,与参照完整性约束不符   
  14.   
  15. 约束方式测试   
  16. insert into t_user values (1, 'qianxin', 1);   
  17. insert into t_user values (2, 'yiyu', 2);   
  18. insert into t_user values (3, 'dai', 2);   
  19. delete from t_group where id=2;              --错误,从表中有相关引用,因此主表中无法删除   
  20. update t_group set id=2 where id=1;          --错误,从表中有相关引用,因此主表中无法修改  

本文由金沙官网线上发布于数据库,转载请注明出处:【金沙官网线上】转载--外键三种作用方式

您可能还会对下面的文章感兴趣: