MySQL外鍵約束的實例講解
MySQL的外鍵約束是用來在兩個表之間建立鏈接的,其中一個表發(fā)生變化,另外一個表也發(fā)生變化。從這個特點來看,它主要是為了保證表數(shù)據(jù)的一致性和完整性的。
對于兩個通過外鍵關聯(lián)的表,相關聯(lián)字段中主鍵所在的表是主表,也稱之為父表,外鍵所在的表是從表,也稱之為子表,定義外鍵的時候需要遵守幾個規(guī)則:
1、父表必須已經(jīng)存在于數(shù)據(jù)庫中,或者是當前正在創(chuàng)建的表。如果是后一種情況,則父表與子表是同一個表,這樣的表稱為自參照表,這種結構稱為自參照。
2、必須為父表定義主鍵。
3、主鍵不能包含空值,但允許在外鍵中出現(xiàn)空值。也就是說,只要外鍵的每個非空值出現(xiàn)在指定的主鍵中,這個外鍵的內(nèi)容就是正確的。
4、外鍵中列的數(shù)目必須和父表的主鍵中列的數(shù)目相同。
5、外鍵中列的數(shù)據(jù)類型必須和父表主鍵中對應列的數(shù)據(jù)類型相同。說這么多比較籠統(tǒng),還是看看例子吧。
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)
這里我們創(chuàng)建兩個表,一個是fk_test_1,一個是fk_test_2,其中fk_test_2的uid列上設置外鍵,關聯(lián)fk_test_1的表的id列,這里很明顯,fk_test_1是父表,而fk_test_2是子表,接下來我們進行數(shù)據(jù)插入實驗。
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`))
先在主表上插入兩條數(shù)據(jù),分別是id=1和id=2的數(shù)據(jù),然后再子表插入數(shù)據(jù),子表插入uid=1和uid=2的數(shù)據(jù)都能成功,而要插入uid=3的數(shù)據(jù)時提示失敗,也就是說,默認情況下,子表進行插入時,插入的外鍵關聯(lián)字段值必須是父表被關聯(lián)的列包含的值。注意這里的默認情況,后續(xù)會進行說明。
再來看看刪除的情況,
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上進行刪除,沒有出現(xiàn)任何問題,而在父表fk_test_1上刪除時,顯示無法刪除id=1的值,原因是有一個外鍵約束存在,也就是說,默認情況下,在父表進行刪除時,無法直接刪除子表中已經(jīng)存在依賴關聯(lián)的列值。注意這里的默認情況,下面將會說明。
既然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父表的主鍵列還是不能成功執(zhí)行,但是update其他的列,可以成功執(zhí)行。
到這里,我們已經(jīng)知道,外鍵的存在是為了保證數(shù)據(jù)的完整和統(tǒng)一性,但是也帶來了一點問題,那就是父表中凡是被子表依賴的列,都沒辦法刪除了,這不是我們想要的,有一些數(shù)據(jù)確實會過期,我們有刪除的需求,那么這個時候應該怎么辦?
在上面的測試中,我們反復提到一個詞,就是默認情況,我們沒有設置外鍵的刪除和更新規(guī)則,這里mysql幫我們使用了最嚴格的的規(guī)則,那就是restrict,其實還有其他一些規(guī)則,這里全部列出來:
- delete父表的情況:
cascade,set null,no action,restrict
- update父表的情況:
cascade,set null,no action,restrict
其中
- restrict是默認操作,它表示拒絕父表刪除或者修改外鍵已經(jīng)被子表所依賴的列,這是最安全的設置;
- cascade表示在父表發(fā)生刪除的時候直接刪除子表的記錄,這是最危險的設置;
- set null表示父表刪除的時候,對子表進行null值處理;
- no action表示父表刪除的時候,子表不進行任何改動。
設置關聯(lián)的語法如下:
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}]
現(xiàn)在我們測試一下這其他三種情況,首先看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_2\G *************************** 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的作用,也就是級聯(lián)刪除。
在看一眼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的情況也是類似,只不過是子表的記錄沒有發(fā)生任何改動。
以上是父表進行delete的操作,當父表進行update的時候,子表可以選擇的情況也有以上四種,和delete基本保持一致,這里不再贅述。有興趣可以自己測試一發(fā)。
最后,說明一點,子表的外鍵列可以為空值。
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)
以上就是MySQL外鍵約束的實例講解的詳細內(nèi)容,更多關于MySQL外鍵約束的資料請關注腳本之家其它相關文章!
相關文章
MySQL函數(shù)一覽_MySQL函數(shù)全部匯總
下面小編就為大家?guī)硪黄狹ySQL函數(shù)一覽_MySQL函數(shù)全部匯總。小編覺得挺不錯的,現(xiàn)在就分享給大家,也給大家做個參考。一起跟隨小編過來看看吧2016-06-06MySQL數(shù)據(jù)庫表被鎖、解鎖以及刪除事務詳解
這篇文章主要給大家介紹了關于MySQL數(shù)據(jù)庫表被鎖、解鎖以及刪除事務的相關資料,需要的朋友可以參考下2022-05-05mysql 8.0.18 壓縮包安裝及忘記密碼重置所遇到的坑
這篇文章主要介紹了mysql 8.0.18 壓縮包安裝及忘記密碼重置所遇到的坑,本文給大家介紹的非常詳細,具有一定的參考借鑒價值,需要的朋友可以參考下2019-12-12