重溫MySQL外鍵約束

• 2019 年 11 月 6 日
• 筆記

1、父表必須已經存在於資料庫中，或者是當前正在創建的表。如果是後一種情況，則父表與子表是同一個表，這樣的表稱為自參照表，這種結構稱為自參照。 2、必須為父表定義主鍵。 3、主鍵不能包含空值，但允許在外鍵中出現空值。也就是說，只要外鍵的每個非空值出現在指定的主鍵中，這個外鍵的內容就是正確的。 4、外鍵中列的數目必須和父表的主鍵中列的數目相同。 5、外鍵中列的數據類型必須和父表主鍵中對應列的數據類型相同。說這麼多比較籠統，還是看看例子吧。

mysql:yeyztest ::>>create table fk_test_1(      -> id int not null primary key auto_increment,      -> name varchar() default '');  Query OK,  rows affected (0.10 sec)    mysql:yeyztest ::>>create table fk_test_2(      -> id int not null primary key auto_increment,      -> uid int,      -> foreign key fk_uid(uid) references fk_test_1(id));  Query OK,  rows affected (0.06 sec)  

這裡我們創建兩個表，一個是fk_test_1，一個是fk_test_2,其中fk_test_2的uid列上設置外鍵，關聯fk_test_1的表的id列，這裡很明顯，fk_test_1是父表，而fk_test_2是子表，接下來我們進行數據插入實驗。

mysql:yeyztest ::>>insert into fk_test_1 values (,'aaa'),(,'bbb');  Query OK,  rows affected (0.00 sec)  Records:   Duplicates:   Warnings:    mysql:yeyztest ::>>select * from fk_test_1;  +----+------+  | id | name |  +----+------+  |   | aaa  |  |   | bbb  |  +----+------+   rows in set (0.00 sec)    mysql:yeyztest ::>>insert into fk_test_2 values (,),(,);  Query OK,  rows affected (0.00 sec)  Records:   Duplicates:   Warnings:    mysql:yeyztest ::>>insert into fk_test_2 values (,);  Query OK,  row affected (0.00 sec)      mysql:yeyztest ::>>insert into fk_test_2 values (,);  ERROR  (): Cannot add or update a child row: a foreign key constraint fails (`yeyztest`.`fk_test_2`, CONSTRAINT `fk_test_2_ibfk_1` FOREIGN KEY (`uid`) REFERENCES `fk_test_1` (`id`))  

先在主表上插入兩條數據，分別是id=1和id=2的數據，然後再子表插入數據，子表插入uid=1和uid=2的數據都能成功，而要插入uid=3的數據時提示失敗，也就是說，默認情況下，子表進行插入時，插入的外鍵關聯欄位值必須是父表被關聯的列包含的值。注意這裡的默認情況，後續會進行說明。

再來看看刪除的情況，

mysql:yeyztest ::>>select * from fk_test_2 ;  +----+------+  | id | uid  |  +----+------+  |   |     |  |   |     |  |   |     |  +----+------+   rows in set (0.00 sec)    mysql:yeyztest ::>>delete from fk_test_2 where id=;  Query OK,  row affected (0.00 sec)    mysql:yeyztest ::>>select * from fk_test_1 ;  +----+------+  | id | name |  +----+------+  |   | aaa  |  |   | bbb  |  +----+------+   rows in set (0.00 sec)    mysql:yeyztest ::>>delete from fk_test_1 where id=;  ERROR  (): Cannot delete or update a parent row: a foreign key constraint fails (`yeyztest`.`fk_test_2`, CONSTRAINT `fk_test_2_ibfk_1` FOREIGN KEY (`uid`) REFERENCES `fk_test_1` (`id`))  

可以看到，在子表fk_test_2上進行刪除，沒有出現任何問題，而在父表fk_test_1上刪除時，顯示無法刪除id=1的值，原因是有一個外鍵約束存在，也就是說，默認情況下，在父表進行刪除時，無法直接刪除子表中已經存在依賴關聯的列值。注意這裡的默認情況，下面將會說明。

既然delete不成功，試試update，

mysql:yeyztest ::>>update fk_test_1 set id= where id=;  ERROR  (): Cannot delete or update a parent row:  a foreign key constraint fails (`yeyztest`.`fk_test_2`,  CONSTRAINT `fk_test_2_ibfk_1` FOREIGN KEY (`uid`) REFERENCES `fk_test_1` (`id`))    mysql:yeyztest ::>>update fk_test_1 set name='ccc' where id=;  Query OK,  row affected (0.00 sec)  Rows matched:   Changed:   Warnings:  

可以看到，update父表的主鍵列還是不能成功執行，但是update其他的列，可以成功執行。

到這裡，我們已經知道，外鍵的存在是為了保證數據的完整和統一性，但是也帶來了一點問題，那就是父表中凡是被子表依賴的列，都沒辦法刪除了，這不是我們想要的，有一些數據確實會過期，我們有刪除的需求，那麼這個時候應該怎麼辦？

在上面的測試中，我們反覆提到一個詞，就是默認情況，我們沒有設置外鍵的刪除和更新規則，這裡mysql幫我們使用了最嚴格的的規則，那就是restrict，其實還有其他一些規則，這裡全部列出來：

delete父表的情況：

cascade，set null，no action，restrict

update父表的情況：

cascade，set null，no action，restrict

其中

restrict是默認操作，它表示拒絕父表刪除或者修改外鍵已經被子表所依賴的列，這是最安全的設置；

cascade表示在父表發生刪除的時候直接刪除子表的記錄，這是最危險的設置；

set null表示父表刪除的時候，對子表進行null值處理；

no action表示父表刪除的時候，子表不進行任何改動。

設置關聯的語法如下：

alter table 表名 add constraint FK_ID foreign key (外鍵欄位名) references 外表表名 (主鍵欄位名)  [on delete {cascade | set null | no action| restrict}]  [on update {cascade | set null | no action| restrict}]  

現在我們測試一下這其他三種情況，首先看cascade的情況：

mysql:yeyztest ::>>select * from fk_test_1;  +----+------+  | id | name |  +----+------+  |   | ccc  |  |   | bbb  |  +----+------+   rows in set (0.00 sec)    mysql:yeyztest ::>>select * from fk_test_2;  +----+------+  | id | uid  |  +----+------+  |   |     |  |   |     |  +----+------+   rows in set (0.00 sec)    mysql:yeyztest ::>>show create table fk_test_2G  *************************** 1. row ***************************         Table: fk_test_2  Create Table: CREATE TABLE `fk_test_2` (    `id` int() NOT NULL AUTO_INCREMENT,    `uid` int() DEFAULT NULL,    PRIMARY KEY (`id`),    KEY `fk_uid` (`uid`),    CONSTRAINT `fk_test_2_ibfk_1` FOREIGN KEY (`uid`) REFERENCES `fk_test_1` (`id`)  ) ENGINE=InnoDB AUTO_INCREMENT= DEFAULT CHARSET=utf8   row in set (0.00 sec)    mysql:yeyztest ::>>alter table fk_test_2 drop foreign key fk_test_2_ibfk_1;  Query OK,  rows affected (0.02 sec)  Records:   Duplicates:   Warnings:    mysql:yeyztest ::>>alter table fk_test_2 add constraint fk_uid foreign key (uid) references fk_test_1(id) on delete cascade;  Query OK,  rows affected (0.03 sec)  Records:   Duplicates:   Warnings:    #######################################  ####此處刪除父表id=的記錄，查看子表的結果###  #######################################  mysql:yeyztest ::>>delete from fk_test_1 where id=;  Query OK,  row affected (0.00 sec)    mysql:yeyztest ::>>select * from fk_test_1 ;  +----+------+  | id | name |  +----+------+  |   | ccc  |  +----+------+   row in set (0.00 sec)    mysql:yeyztest ::>>select * from fk_test_2 ;  +----+------+  | id | uid  |  +----+------+  |   |     |  +----+------+   row in set (0.00 sec)  

可以看到，一開始，父表的值包含id=1和id=2的值，子表的值包含uid=2和uid=1的值，當我們刪除父表的id=2的值之後，子表中uid=2的值也直接被刪除了。這就是cascade的作用，也就是級聯刪除。

在看一眼set null的情況：

mysql:yeyztest ::>>alter table fk_test_2 drop foreign key fk_uid;  Query OK,  row affected (0.02 sec)  Records:   Duplicates:   Warnings:    mysql:yeyztest ::>>alter table fk_test_2 add CONSTRAINT `fk_uid` FOREIGN KEY (`uid`) REFERENCES `fk_test_1` (`id`) ON DELETE set null;  Query OK,  row affected (0.03 sec)  Records:   Duplicates:   Warnings:    mysql:yeyztest ::>>delete from fk_test_1 where id=;  Query OK,  row affected (0.00 sec)    mysql:yeyztest ::>>select *from fk_test_1 ;  Empty set (0.00 sec)    mysql:yeyztest ::>>select *from fk_test_2 ;  +----+------+  | id | uid  |  +----+------+  |   | NULL |  +----+------+   row in set (0.00 sec)  

可以看到，設置了set null之後，當父表刪除id=1的值時，子表的uid的值變成了null，而沒有刪除記錄。

no action的情況也是類似，只不過是子表的記錄沒有發生任何改動。

以上是父表進行delete的操作，當父表進行update的時候，子表可以選擇的情況也有以上四種，和delete基本保持一致，這裡不再贅述。有興趣可以自己測試一發。

最後，說明一點，子表的外鍵列可以為空值。

mysql:yeyztest ::>>insert into fk_test_1 values (,);  Query OK,  row affected (0.00 sec)    mysql:yeyztest ::>>select *from fk_test_2 ;  +----+------+  | id | uid  |  +----+------+  |   | NULL |  +----+------+   row in set (0.00 sec)    mysql:yeyztest ::>>insert into fk_test_2 values (,NULL);  Query OK,  row affected (0.00 sec)    mysql:yeyztest ::>>insert into fk_test_2 values (,NULL);  Query OK,  row affected (0.00 sec)    mysql:yeyztest ::>>select * from fk_test_2;  +----+------+  | id | uid  |  +----+------+  |   | NULL |  |   | NULL |  |   | NULL |  +----+------+   rows in set (0.00 sec)