mysql 外鍵約束

標籤：font   sql   ima   ict   varchar   linux   cascade   innodb   span   

外鍵的作用：外鍵具有保持資料完整性和一致性的機制。

① 外鍵只能指向主表或本表的主鍵列或者unique
② 外鍵的資料類型應該與它指向的列類型一致
③ 外鍵的值：NULL 或者 指向列中存在的值

##############準備##############

建立一個組表作為主表，命名為t_group：

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‘); 

##############示範##############

三種外鍵約束模式：級聯（cascade）、置空（set null）、禁止（no action / restrict 預設）

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 cascade on update cascade   );#（1）從表中的外索引值必須在主表中存在#（2）從表中的外鍵類型一定要和參照鍵（主表中的主鍵）類型一致，若為數#字，則要完全一致；若為字元，字元個數可以不一致。
#參照完整性測試：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.置空方式（主表中的記錄被刪除或主鍵被更新，從表則會被設定為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.禁止方式（從表有引用時，主表無法刪除或更新）

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

注意：外鍵約束需要預設引擎為innodb引擎。

linux環境下可以通過修改設定檔/usr/share/mysql/my-default.cnf來修改預設引擎

mysql 外鍵約束