mysql 外鍵（foreign key）的詳解和實例「建議收藏」

一、基本概念

1、MySQL中“鍵”和“索引”的定義相同，所以外鍵和主鍵一樣也是索引的一種。不同的是MySQL會自動為所有表的主鍵進行索引，但是外鍵字段必須由用戶進行明確的索引。用于外鍵關系的字段必須在所有的參照表中進行明確地索引，InnoDB不能自動地創建索引。

2、外鍵可以是一對一的，一個表的記錄只能與另一個表的一條記錄連接，或者是一對多的，一個表的記錄與另一個表的多條記錄連接。

3、如果需要更好的性能，并且不需要完整性檢查，可以選擇使用MyISAM表類型，如果想要在MySQL中根據參照完整性來建立表并且希望在此基礎上保持良好的性能，最好選擇表結構為innoDB類型。

4、外鍵的使用條件

① 兩個表必須是InnoDB表，MyISAM表暫時不支持外鍵

② 外鍵列必須建立了索引，MySQL 4.1.2以后的版本在建立外鍵時會自動創建索引，但如果在較早的版本則需要顯式建立；

③ 外鍵關系的兩個表的列必須是數據類型相似，也就是可以相互轉換類型的列，比如int和tinyint可以，而int和char則不可以；

5、外鍵的好處：可以使得兩張表關聯，保證數據的一致性和實現一些級聯操作。

二、使用方法

1、創建外鍵的語法：

外鍵的定義語法：

[CONSTRAINT symbol] FOREIGN KEY [id] (index_col_name, …)

REFERENCES tbl_name (index_col_name, …)

[ON DELETE {RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT}]

[ON UPDATE {RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT}]

該語法可以在 CREATE TABLE 和 ALTER TABLE 時使用，如果不指定CONSTRAINT symbol，MYSQL會自動生成一個名字。

ON DELETE、ON UPDATE表示事件觸發限制，可設參數：

① RESTRICT（限制外表中的外鍵改動，默認值）

② CASCADE（跟隨外鍵改動）

③ SET NULL（設空值）

④ SET DEFAULT（設默認值）

⑤ NO ACTION（無動作，默認的）

2、示例

1）創建表1

create table repo_table(

repo_id char(13) not null primary key,

repo_name char(14) not null)

type=innodb;

創建表2

mysql> create table busi_table(

-> busi_id char(13) not null primary key,

-> busi_name char(13) not null,

-> repo_id char(13) not null,

-> foreign key(repo_id) references repo_table(repo_id))

-> type=innodb;

2）插入數據

insert into repo_table values(“12″,”sz”); //success

insert into repo_table values(“13″,”cd”); //success

insert into busi_table values(“1003″,”cd”, “13”); //success

insert into busi_table values(“1002″,”sz”, “12”); //success

insert into busi_table values(“1001″,”gx”, “11”); //failed,提示：

ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`smb_man`.`busi_table`, CONSTRAINT `busi_table_ibfk_1` FOREIGN KEY (`repo_id`) REFERENCES `repo_table` (`repo_id`))

3）增加級聯操作

mysql> alter table busi_table

-> add constraint id_check

-> foreign key(repo_id)

-> references repo_table(repo_id)

-> on delete cascade

-> on update cascade;

—–

ENGINE=InnoDB DEFAULT CHARSET=gb2312； //另一種方法，可以替換type=innodb;

3、相關操作

外鍵約束（表2）對父表（表1）的含義:

在父表上進行update/delete以更新或刪除在子表中有一條或多條對應匹配行的候選鍵時，父表的行為取決于：在定義子表的外鍵時指定的on update/on delete子句。

關鍵字	含義
CASCADE	刪除包含與已刪除鍵值有參照關系的所有記錄
SET NULL	修改包含與已刪除鍵值有參照關系的所有記錄，使用NULL值替換(只能用于已標記為NOT NULL的字段)
RESTRICT	拒絕刪除要求，直到使用刪除鍵值的輔助表被手工刪除，并且沒有參照時(這是默認設置，也是最安全的設置)
NO ACTION	啥也不做

4、其他

在外鍵上建立索引:

index repo_id (repo_id),

foreign key(repo_id) references repo_table(repo_id))

摘要: 外鍵具有保持數據完整性和一致性的機制，目前MySQL只在InnoDB引擎下支持，下面實例下一個小操作來說明下外鍵的關聯操作，用來保持數據的完整性和一致性。

外鍵具有保持數據完整性和一致性的機制，對業務處理有著很好的校驗作用。

============================白話文簡介=================================

user 表：id 為主鍵

profile 表： uid 為主鍵

簡單來說，若表 profile 的 uid 列 作為外鍵（外建名稱：user_profile），參考的主表的列（references）為 user 表 的 id，且聯動刪除更新操作（on delete cascade on update cascade），則 user 表中刪除 id 為 1 的記錄，會聯動刪除 profile 表中 uid 為 1 的記錄。user 表中更新 id 為 1 的記錄至 id 為 2，則profile 表中 uid 為 1 的記錄也會被聯動更新至uid 為 2，這樣遍保持了數據的完整性和一致性。

B 存在外鍵 b_f_k，以 A 表的 a_k 作為參照列，則 A 為主表，B 為從表，A 中某記錄更新或刪除時將會聯動B 中外鍵與其關聯對應的記錄做更新或刪除操作。

altertable`profile`addconstraint`user_profile`foreignkey(`uid`)references`user`(`id`)ondeletecascadeonupdatecascade;

在 profile 中為 uid 列添加名為 user_profile 的外鍵，且此外鍵的參照為 user 表的 id 列，關聯的操作為刪除和更新

=============================正文====================================

1、表引擎必須為InnoDB，MyISAM不支持

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

altertableBaddconstraint`b_foreign_key_name`foreignkey(`bfk`) referencesA(`afk`)ondeletenoactiononupdatenoaction;

時 b_foreign_key_name 為外鍵名，bfk字段和afk字段都必須存在索引

3、外表為約束表，約束著含有外鍵的被約束表，即 B 含有一個以 A 作為參考表的外鍵，則 A 為主 B 為從，若關聯on delete on update等動作，則 A 變更 B 會被變更，B 怎樣變 A 不必跟隨變動，且表 A 中必須事先存在 B 要插入的數據外鍵列的值，列如 B.bfk作為外鍵 參照 A.ak ，則 B.bfk插入的值必須是 A.ak 中已存在的

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

下面我們以一個簡單的學生信息管理系統的數據表做為實例

先把表和索引加好

//學生表cid作為外鍵關聯班級表pid作為檔案表外鍵的關聯所以這倆貨都得有索引 createtablemy_student( `id`intunsignednotnullauto_incrementprimarykey, `name`varchar(25)notnullcomment'studentname', `pid`intunsignednotnullcomment'studentprofileid', `cid`intunsignednotnullcomment'studentclassid', key`cid`(`cid`)， key`pid`(`pid`) )engine=InnoDBdefaultcharset=utf8auto_increment=1; //班級表id作為學生表外鍵的關聯已為主鍵索引 createtablemy_class( `id`intunsignednotnullauto_incrementprimarykey, `cname`varchar(25)notnullcomment'classname', `info`tinytextnotnulldefault'' )engine=InnoDBdefaultcharset=utf8auto_increment=1; //檔案表id作為外鍵關聯學生表已為主鍵索引 createtablemy_profile( `id`intunsignednotnullauto_incrementprimarykey, `pname`varchar(25)notnullcomment'profilename', `info`tinytextnotnulldefault''comment'studentinfo'， )engine=InnoDBdefaultcharset=utf8auto_increment=1;

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

altertablemy_profileaddconstraintprofile_studentforeignkey(`id`)referencesmy_student(`pid`)ondeletecascadeonupdatenoaction;

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

altertablemy_studentaddconstraintstudent_classforeignkey(`cid`)referencesmy_class(`id`)onupdatecascadeondeletenoaction;

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

//刪除id為1的學生記錄，因檔案表以學生表作為外表約束，且關聯ondeletecascade操作 deletefrommy_studentwhereid=1; 這是外鍵機制自身執行的處理動作 deletefrommy_profilewhereid=(selectpidfrommy_studentwhereid=1); 這是外鍵機制自身執行的處理動作

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

//更新聯動 updatemy_classsetid=5whereid=1; 這是外鍵機制自身執行的處理動作 updatemy_studentsetcid=5wherecid=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

添加外鍵

altertableBaddconstraint`bfk`foreignkey('fk_column_name')referencesA('column_name')ondeletenoactiononupdatenoaction;

刪除外鍵

altertableBdropforeignkey`bfk`;

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