[轉] mysql 外來鍵（Foreign Key）的詳解和例項

摘要: 外來鍵具有保持資料完整性和一致性的機制，業務處理有著很好的校驗作用。目前MySQL只在InnoDB引擎下支援，下面例項下一個小操作來說明下外來鍵的關聯操作，用來保持資料的完整性和一致性。

============正文===============
1、表引擎必須為InnoDB，MyISAM不支援

2、外來鍵必須建立索引（可以為普通、主鍵、唯一，事先不建立的話會自動建立一個普通索引），你要用的外來鍵和參照的外表的鍵，即

alter table B add constraint `b_foreign_key_name` foreign key (`bfk`) 
references A(`afk`) on delete no action on update no action;

時 b_foreign_key_name 為外來鍵名，bfk欄位和afk欄位都必須存在索引
3、外表為約束表，約束著含有外來鍵的被約束表，即 B 含有一個以 A 作為參考表的外來鍵，則 A 為主 B 為從，若關聯on delete on update等動作，則 A 變更 B 會被變更，B 怎樣變 A 不必跟隨變動，且表 A 中必須事先存在 B 要插入的資料外來鍵列的值，列如 B.bfk作為外來鍵 參照 A.afk ，則 B.bfk插入的值必須是 A.afk 中已存在的

4、把3說的簡單點就是若B有以A作為參照的外來鍵，則B中的此欄位的取值只能是A中存在的值，從表B會實時受到主表A的約束，同時若關聯on delete on update等操作則當A中的被參照的欄位發生delete或update時，B中的對應的記錄也會發生delete 或 update操作，完整性。

下面我們以一個簡單的學生資訊管理系統的資料表做為例項

先把表和索引加好

//學生表 cid作為外來鍵關聯班級表 pid作為 檔案表外來鍵的關聯 所以這倆貨都得有索引create table my_student(
   `id` int unsigned not null auto_increment primary key, 
   `name` varchar(25) not null comment 'student name', 
   `pid` int unsigned not null comment 'student profile id', 
   `cid` int unsigned not null comment 'student class id',
    key `cid`(`cid`)， key `pid`(`pid`)
)engine=InnoDB default charset=utf8 auto_increment=1;

//班級表 id作為 學生表外來鍵的關聯 已為主鍵索引
create table my_class( 
   `id` int unsigned not null auto_increment primary key, 
   `cname` varchar(25) not null comment 'class name', 
   `info` tinytext not null default ''
)engine=InnoDB default charset=utf8 auto_increment=1;

//檔案表 id作為外來鍵 關聯 學生表 已為主鍵索引
create table my_profile( 
   `id` int unsigned not null auto_increment primary key,
   `pname` varchar(25) not null comment 'profile name',
   `info` tinytext not null default '' comment 'student info'，
) engine=InnoDB default charset=utf8 auto_increment=1;

這裡我們將my_student作為my_profile的外表，即約束表，即my_profile以自身id作為 外來鍵 關聯 以 my_student 的pid欄位作為參照，關聯delete聯動操作，update不做任何操作，如下

alter table my_profile add constraint profile_student foreign key (`id`) 
references my_student(`pid`) on delete cascade on update no action;

這裡我們將my_class作為my_student的外表，即約束表，即my_student以自身cid作為 外來鍵 關聯 以 my_class 的id欄位作為參照，關聯update聯動操作，delete不做任何操作，如下

alter table my_student add constraint student_class foreign key (`cid`) 
references my_class(`id`) on update cascade on delete no action;

則當我刪除my_student中 id=1 的學生時，其會將my_profile中id為此學生pid的記錄刪掉

//刪除id為1的學生記錄，因檔案表以學生表作為外表約束，且關聯 on delete cascade操作
delete from my_student where id = 1;

//這是外來鍵機制自身執行的處理動作
delete from my_profile where id = (select pid from my_student where id = 1);

則當我更新my_class中 id=1 的班級為5時，其會將my_student中cid=1的學生更新為cid=5

//更新聯動
update my_class set id = 5 where id = 1;

//這是外來鍵機制自身執行的處理動作
update my_student set cid = 5 where cid = 1;

貼出程式碼：
my_profile:

id做為外來鍵，參照my_student以其pid作為關聯，關聯刪除聯動，更新無動作，則檔案表受學生表的刪除約束，當學生表中id為xx的記錄被刪除時，檔案表中id為此記錄pid的記錄也會唄刪除掉。

my_student:

學生表pid作為檔案表的外來鍵關聯所以要建立key pid 索引

以cid作為外來鍵 參照 班級表的id 關聯更新操作 刪除無關聯（用意為當班級的id發生變動時，學生表中每個學生的cid也會關聯更新，這樣即使班級表中的班級id發生變化，學生所屬班級仍然保持著完整且一致）

my_class:

班級表，id作為學生表的外來鍵參照，為主鍵索引

實驗開始：
1、刪除學生表中的某個學生，則將其作為外表參照且關聯刪除聯動操作的檔案表中的記錄也會被刪除掉，關聯關係為
my_profile.id = my_student.pid的記錄

很容易看懂吧，刪除id為22的學生時，他的pid為2，則檔案表裡id為2的記錄也被關聯刪除了

2、修改班級id，學生表cid外來鍵的更新聯動 關聯 班級表中的id，即當我變更班級id時，學生表中的cid也會被更新

很容易看懂吧，四年級的id由4更新為5時，以其作為參照表的學生表中屬於四年級的小紅的cid也由4更新為5。

on delete on update的聯動操作有四種
no action cascade set null restrict

新增外來鍵

alter table B add constraint `bfk` foreign key ('fk_column_name') 
references A('column_name') on delete no action on update no action;

刪除外來鍵

alter table B drop foreign key `bfk`;

大家可以自行百度一下，這裡就不囉嗦了，截稿！

點選可檢視轉載 原文出處！