天天看點

外鍵限制

如果表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));      

并插入兩條記錄:

外鍵限制
  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)方式

外鍵限制
  1. create table t_user (  
  2.     groupid int,  
  3. primary key (id),  
  4. foreign key (groupid) references t_group(id) on delete cascade on update cascade
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);      

參照完整性測試

外鍵限制
  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不存在,與參照完整性限制不符      

限制方式測試

  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)方式

  1. 置空方式  
  2. foreign key (groupid) references t_group(id) on delete set null on update set null
  3. );  
  4. 參照完整性測試  
  5. insert into t_user values (3, 'dai', 3);     --錯誤,無法插入,使用者組3不存在,與參照完整性限制不符
  6. 限制方式測試  
  7. delete from t_group where id=2;              --導緻t_user中的2、3記錄的groupid被設定為NULL
  8. 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)方式

  1. 禁止方式  
  2. foreign key (groupid) references t_group(id) on delete no action on update no action
  3. delete from t_group where id=2;              --錯誤,從表中有相關引用,是以主表中無法删除
  4. update t_group set id=2 where id=1;          --錯誤,從表中有相關引用,是以主表中無法修改
禁止方式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;          --錯誤,從表中有相關引用,是以主表中無法修改      
下一篇: mysql 外鍵

繼續閱讀