MYSQL外鍵約束的參照操作

標籤：

如果表A的主關鍵字是表B中的欄位，則該欄位稱為表B的外鍵，表A稱為主表，表B稱為從表。外鍵是用來實現參照完整性的，不同的外鍵約束方式將可以使兩張表緊密的結合起來，特別是修改或者刪除的級聯操作將使得日常的維護工作更加輕鬆。這裡以MySQL為例，總結一下3種外鍵約束方式的區別和聯絡。

　　這裡以使用者表和使用者組表為例，這是一個典型的多對一關聯性，多個使用者對應於一個使用者組。

　　首先建立使用者組表：

　　建立使用者組表

　　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 (

　　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

　　);

　　參照完整性測試

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

　　置空方式

　　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; #錯誤，從表中有相關引用，因此主表中無法修改

　　註：在MySQL中，restrict方式與no action方式作用相同。

原文出自【位元網】，轉載請保留原文連結：http://soft.chinabyte.com/database/493/12549493.shtml

MYSQL外鍵約束的參照操作