什么是外鍵約束

外鍵是表中的一列或一組列鏈接到另外一張表的一列或一組列。外鍵會在相關(guān)聯(lián)的表中起到約束作用，保證數(shù)據(jù)的一致性和實(shí)現(xiàn)一些級聯(lián)操作。

如果在表A中建立外鍵，關(guān)聯(lián)到表B，那么表B為主表，表A為從表。主表B中的對應(yīng)的列的更新或刪除會聯(lián)動到外鍵所在的表A中的相應(yīng)的列的操作（具體的操作根據(jù)在表A中添加外鍵時的配置不同而不同）

即，建立外鍵的表為從表，被外鍵關(guān)聯(lián)的表是主表。

創(chuàng)建外鍵約束時，會針對本表中相應(yīng)的行自動創(chuàng)建索引。

外鍵的使用條件

1. 外鍵只適用于InnoDB引擎，MyISAM不支持。
2. 外鍵列必須建立了索引，MySQL 4.1.2以后的版本在建立外鍵時會自動創(chuàng)建索引，但如果在較早的版本則需要顯式建立；
3. 外鍵關(guān)系的兩個表的列必須是數(shù)據(jù)類型相似，也就是可以相互轉(zhuǎn)換類型的列，比如int和tinyint可以，而int和char則不可以；

簡單介紹

假設(shè)存在兩張表customers和orders。每一個custormer可以有0個或多個orders，同樣的，每個order都屬于某一個customer。

可以看出custormers和orders表是一對多的關(guān)系。并且這個關(guān)系通過orders表中的外鍵的customer_id列來建立。

在oders表中的custormer_id列鏈接到customers表中的id主鍵列。

此時，customers表通常被稱為主表（父表），orders表被稱為子表。

通常情況下，外鍵都是關(guān)聯(lián)到主表的主鍵列上面。

子表上面可以創(chuàng)建多個外鍵并關(guān)聯(lián)到多個主表的主鍵列上。

一旦外鍵約束就位。外鍵約束的列的值需要在主表的主鍵列上存在，或者為NULL（此時外鍵約束的action是SET NULL）

例如，orders表中的customer_id列的值需要存在與customers表的id列上。oders表中的多個行可以擁有相同的custormer_id

自引用的外鍵

有些時候，子表和主表可能是同一張表。這種情況下外鍵引用的是當(dāng)前表的主鍵

比如下面這張表employees：

employees

其中reportTo字段是一個外鍵字段，它指向本表的主鍵列employeeNumber。

這種關(guān)系允許employees表存儲雇員與管理人員的關(guān)系結(jié)構(gòu)。每個雇員都有0個或1個上級，且每個雇員可以擁有0個或多個下級。

此時，reportTo列上的外鍵就叫做遞歸或自引用外鍵。

創(chuàng)建外鍵的SQL語法

通過CREATE TABLE或ALTER TABLE創(chuàng)建外鍵的基本語法如下：

[CONSTRAINT [symbol]] FOREIGN KEY
    [index_name] (col_name, ...)
    REFERENCES tbl_name (col_name,...)
    [ON DELETE reference_option]
    [ON UPDATE reference_option]

首先在CONSTRAINT關(guān)鍵字后面指定外鍵約束的名字。如果省略名字，那么MySQL會為此外鍵約束自動創(chuàng)建一個名字。

接下來，通過FOREIGN KEY關(guān)鍵字來指定此外鍵的列，多個列的話用逗號分隔開來，同樣的外鍵的名字也是可省略的。

第三步指定主表和主表上被引用到的列，多列用逗號分隔。

最后，指定外鍵在子表和主表之間聯(lián)動的動作（action），這些動作分為ON DELETE和ON UPDATE。reference_option表示子表中此外鍵關(guān)聯(lián)的列所采取的行動，當(dāng)主表中被引用的列被刪除（ON DELETE）或更新（ON UPDATE）時。

MySQL有5種reference options：CASCADE, SET NULL, NO ACTION, RESTRICT, and SET DEFAULT：

• CASCADE：級聯(lián)操作，如果父表中的一行被刪除或更新，子表會自動跟著刪除或更新。
• SET NULL：如果父表中的行被刪除或更新，子表中相應(yīng)的列的值被設(shè)置為NULL。
• RESTRICT：如果父表中被外鍵引用的列的值在子表中存在相應(yīng)的行與之匹配，MySQL拒絕父表的這個刪除或更新操作。
• NO ACTION：同RESTRICT
• SET DEFAULT：MySQL的語法分析器可以識別，但是InnoDB和NDB引擎不支持。

實(shí)際上，MySQL支持三種actions：RESTRICT, CASCADE and SET NULL。

如果沒有設(shè)置ON DELETE和ON UPDATE，那么默認(rèn)的action是RESTRICT。

開始實(shí)驗(yàn)

下面通過實(shí)際的操作來體驗(yàn)外鍵的功能。

創(chuàng)建兩張表custormers和orders

CREATE TABLE customers(
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL
) ENGINE=INNODB;

CREATE TABLE orders(
    id INT AUTO_INCREMENT PRIMARY KEY,
    customer_id INT,
    order_info VARCHAR(255)
) ENGINE=INNODB;

RESTRICT & NO ACTION actions

在orders表中創(chuàng)建外鍵，使用默認(rèn)的RESTRICTaction。

ALTER TABLE orders
    ADD FOREIGN KEY (customer_id)
    REFERENCES customers(id);

未指定action時，默認(rèn)ON DELETE和ON UPDATE都是RESTRICT

在customers表中插入數(shù)據(jù)：

INSERT INTO customers (name) values ('andy'),('jerry');

此時customser表中的數(shù)據(jù)為：

在orders表中插入新的行：

INSERT INTO orders (customer_id,order_info) values (1,'info');

插入成功，因?yàn)閕d為1的customer_id存在于customers表中。

那么接下來在orders表中插入一條customer_id不存在的數(shù)據(jù)看看會發(fā)生什么：

INSERT INTO orders (customer_id,order_info) values (3,'info');

出現(xiàn)了如下的報錯信息：

ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`test`.`orders`, CONSTRAINT `orders_ibfk_1` FOREIGN KEY (`customer_id`) REFERENCES `customers` (`id`))

那么接下來更新一下customers表中id為1的行試試看：

UPDATE customers set id = 5 where id = 1;

出現(xiàn)了報錯：

ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`test`.`orders`, CONSTRAINT `orders_ibfk_1` FOREIGN KEY (`customer_id`) REFERENCES `customers` (`id`))

因?yàn)?code>RESTRICTaction，如果子表中存在父表中外鍵約束引用到的列的值，那么mysql會阻止了父表的更新和刪除操作。

下面的更新就不會出錯，因?yàn)閕d為2的customer_id不存在于oders表中。

UPDATE customers set id = 5 where id = 2;

CASCADE action

刪除外鍵約束

需要使用兩條命令來刪除：

//刪除外鍵約束
ALTER TABLE orders DROP FOREIGN KEY `orders_ibfk_1`;
//刪除創(chuàng)建外鍵約束時自動創(chuàng)建的索引
ALTER TABLE orders DROP INDEX `customer_id`;

注意刪除外鍵約束的時候，使用語句ALTER TABLE example_table DROP FOREIGN KEY `constraint_name`;這里的constraint_name是外鍵約束的名字，而不是外鍵的名字，如果創(chuàng)建約束的時候沒有指定名字，那么可以通過SHOW CREATE TABLE example_table命令查看。
同時，上面的命令刪除了外鍵約束并不會同步刪除創(chuàng)建外鍵約束是的對應(yīng)列的索引，所以需要額外的一條命令去刪除它。

創(chuàng)建新的外鍵約束，使用CASCADEaction：

ALTER TABLE orders
    ADD FOREIGN KEY (customer_id)
    REFERENCES customers(id)
    ON UPDATE CASCADE
    ON DELETE CASCADE;

此時orders表中的數(shù)據(jù)如下：

將custorms表中的id為1的行改為10：

UPDATE customers set id = 10 where id = 1;

更新成功之后，查看orders表中的數(shù)據(jù)：

發(fā)現(xiàn)其customer_id列的值也同步更新為了10

接下來刪除customsers表中id為10的行：

DELETE FROM customers where id = 10;

執(zhí)行成功之后查看orders表中的數(shù)據(jù)為空，其與主表關(guān)聯(lián)的行數(shù)據(jù)也被刪除了。

SET NULL action

刪除外鍵約束

需要使用兩條命令來刪除：

//刪除外鍵約束
ALTER TABLE orders DROP FOREIGN KEY `orders_ibfk_1`;
//刪除創(chuàng)建外鍵約束時自動創(chuàng)建的索引
ALTER TABLE orders DROP INDEX `customer_id`;

創(chuàng)建新的外鍵約束，使用CASCADEaction：

ALTER TABLE orders
    ADD FOREIGN KEY (customer_id)
    REFERENCES customers(id)
    ON UPDATE SET NULL
    ON DELETE SET NULL;

這里注意創(chuàng)建外鍵約束的列customer_id不能使用NOT NULL語句，不然無法創(chuàng)建SET NULLaction的外鍵約束

往orders插入新的數(shù)據(jù)（上個步驟的刪除行操作已經(jīng)將orders表的數(shù)據(jù)刪除了）

INSERT INTO orders (customer_id,order_info) values (5,'info');

更新customers表中id為5個行，將id改為50：

UPDATE customers set id = 50 where id = 5;

查看orders表：

原先customer_id為5的那一行數(shù)據(jù)，現(xiàn)在值變?yōu)榱?code>NULL，這是因?yàn)?code>ON UPDATE SET NULL起作用了

往customsers和orders表中都插入新的測試數(shù)據(jù)

INSERT INTO customers (id,name) values (8,'andy');
INSERT INTO orders (customer_id,order_info) values (8,'info');

現(xiàn)在customsers表和orders表的數(shù)據(jù)分別如下：

刪除customers表中新增的id為8的數(shù)據(jù)：

DELETE FROM customers where id = 8;

此時orders表中的相對應(yīng)的行的customer_id列的值變?yōu)榱?code>NULL：

這是因?yàn)橥怄I約束ON DELETE SET NULL action 起了作用

禁用外鍵檢查

有些時候當(dāng)我們需要從一個表中導(dǎo)入數(shù)據(jù)是，如果存在外鍵，那么導(dǎo)入和修改的順序就不能錯亂，必須嚴(yán)格遵循先導(dǎo)入主表然后再導(dǎo)入子表的順序。此時可以通過改變變量的形式來臨時禁用外鍵檢查。

禁用外鍵檢查：

SET foreign_key_checks = 0;

啟用外鍵檢查：

SET foreign_key_checks = 1;

參考

An Essential Guide to MySQL Foreign Key By Practical Examples
Using FOREIGN KEY Constraints
mysql 外鍵（foreign key）的詳解和實(shí)例