MySQL數(shù)據(jù)庫中外鍵(foreign?key)用法詳解
更新時間：2023年10月27日 08:49:32 作者：知其黑、受其白
這篇文章主要給大家介紹了關(guān)于MySQL數(shù)據(jù)庫中外鍵(foreign?key)的相關(guān)資料,MySQL 外鍵約束可以用來保證表與表之間的關(guān)系完整性,文中通過代碼介紹的非常詳細(xì),需要的朋友可以參考下
前言
在MySQL中，我們都對主鍵比較了解，知道主鍵的主要作用是唯一區(qū)分表中的各個行；
但是，對于外鍵（foreign key） 比較陌生。
一、外鍵作用及其限制條件
1 外鍵的定義
外鍵是某個表中的一列，它包含在另一個表的主鍵中。
外鍵也是索引的一種，是通過一張表中的一列指向另一張表中的主鍵，來對兩張表進行關(guān)聯(lián)。
一張表可以有一個外鍵，也可以存在多個外鍵，與多張表進行關(guān)聯(lián)。
2 外鍵的作用
外鍵的主要作用是保證數(shù)據(jù)的一致性和完整性，并且減少數(shù)據(jù)冗余。
主要體現(xiàn)在以下兩個方面：
阻止執(zhí)行
- 從表插入新行，其外鍵值不是主表的主鍵值便阻止插入。
- 從表修改外鍵值，新值不是主表的主鍵值便阻止修改。
- 主表刪除行，其主鍵值在從表里存在便阻止刪除(要想刪除，必須先刪除從表的相關(guān)行)。
- 主表修改主鍵值，舊值在從表里存在便阻止修改(要想修改，必須先刪除從表的相關(guān)行)。
級聯(lián)執(zhí)行
- 主表刪除行，連帶從表的相關(guān)行一起刪除。
- 主表修改主鍵值，連帶從表相關(guān)行的外鍵值一起修改。
3 外鍵創(chuàng)建限制
父表必須已經(jīng)存在于數(shù)據(jù)庫中，或者是當(dāng)前正在創(chuàng)建的表。
如果是后一種情況，則父表與子表是同一個表，這樣的表稱為自參照表，這種結(jié)構(gòu)稱為自參照完整性。
必須為父表定義主鍵。
外鍵中列的數(shù)目必須和父表的主鍵中列的數(shù)目相同。
兩個表必須是 InnoDB 表，MyISAM 表暫時不支持外鍵。
外鍵列必須建立了索引，MySQL 4.1.2 以后的版本在建立外鍵時會自動創(chuàng)建索引，但如果在較早的版本則需要顯式建立。
外鍵關(guān)系的兩個表的列必須是數(shù)據(jù)類型相似，也就是可以相互轉(zhuǎn)換類型的列，比如 int 和tinyint 可以，而 int 和 char 則不可以；
二、外鍵創(chuàng)建方法
可以在創(chuàng)建表時創(chuàng)建外鍵，也可以在已有的表中增加外鍵。
我們主要講第二種方式創(chuàng)建外鍵。
1 創(chuàng)建外鍵的語法
ALTER TABLE 表名 ADD CONSTRAINT 外鍵名 FOREIGN KEY(外鍵字段名)
REFERENCES 外表表名(主鍵字段名)
[ON DELETE {RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT}]
[ON UPDATE {RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT}]
其中，ON DELETE 和 ON UPDATE 表示事件觸發(fā)限制，各參數(shù)意義如下：
| 參數(shù) | 意義 |
|---|---|
| RESTRICT | 限制外表中的外鍵改動（默認(rèn)值，也是最安全的設(shè)置） |
| CASCADE | 跟隨外鍵改動 |
| SET NULL | 設(shè)為null值 |
| NO ACTION | 無動作 |
| SET DEFAULT | 設(shè)為默認(rèn)值 |
2 舉例
（1）創(chuàng)建兩張表
CREATE TABLE student ( id int (11) primary key auto_increment, name char(255),sex char(255), age int(11) )charset utf8; CREATE TABLE student_score ( id int (11) primary key auto_increment, class char(255),score char(255), student_id int(11) )charset utf8;
（2）創(chuàng)建外鍵
ALTER TABLE student_score ADD CONSTRAINT s_id FOREIGN KEY (student_id) REFERENCES student (id);
（3）查看表結(jié)構(gòu)
SHOW CREATE TABLE student; SHOW CREATE TABLE student_score;
CREATE TABLE `student` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` char(255) DEFAULT NULL, `sex` char(255) DEFAULT NULL, `age` int(11) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;
CREATE TABLE `student_score` ( `id` int(11) NOT NULL AUTO_INCREMENT, `class` char(255) DEFAULT NULL, `score` char(255) DEFAULT NULL, `student_id` int(11) DEFAULT NULL, PRIMARY KEY (`id`), KEY `s_id` (`student_id`), CONSTRAINT `s_id` FOREIGN KEY (`student_id`) REFERENCES `student` (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;
三、驗證外鍵作用
1 先向主表中添加數(shù)據(jù)
再向從表中添加數(shù)據(jù)（從表中的外鍵已在主表中存在），正常運行。
#向 student 表中添加數(shù)據(jù)
INSERT INTO student (NAME, sex, age)
VALUES
('小明', '男', '20');
#向 student_score 表中添加數(shù)據(jù)
INSERT INTO student_score (class, score, student_id)
VALUES
('語文', '100', 1),
('數(shù)學(xué)', '99', 1),
('英語', '98', 1);
數(shù)據(jù)插入正常，無報錯、警告信息。
2 觸發(fā)限制使用默認(rèn)值 RESTRICT 的情況下
（1）從表插入新行，外鍵值不在主表中，被阻止
INSERT INTO student_score (class, score, student_id)
VALUES
('語文', '100', 2);
ERROR 1452 (23000) : Cannot ADD OR UPDATE a child ROW : a FOREIGN KEY CONSTRAINT fails ( `tts`.`student_score`, CONSTRAINT `s_id` FOREIGN KEY (`student_id`) REFERENCES `student` (`id`) )
（2）從表修改外鍵值，新值不是主表的主鍵值，阻止修改
UPDATE student_score SET student_id = 2 WHERE student_id = 1;
ERROR 1452 (23000) : Cannot ADD OR UPDATE a child ROW : a FOREIGN KEY CONSTRAINT fails ( `tts`.`student_score`, CONSTRAINT `s_id` FOREIGN KEY (`student_id`) REFERENCES `student` (`id`) )
（3）主表刪除行，其主鍵值在從表里存在便阻止刪除(要想刪除，必須先刪除從表的相關(guān)行)
DELETE FROM student WHERE id = 1;
ERROR 1451 (23000) : Cannot DELETE OR UPDATE a parent ROW : a FOREIGN KEY CONSTRAINT fails ( `tts`.`student_score`, CONSTRAINT `s_id` FOREIGN KEY (`student_id`) REFERENCES `student` (`id`) )
（4）主表修改主鍵值，舊值在從表里存在便阻止修改(要想修改，必須先刪除從表的相關(guān)行)
UPDATE student SET id = 2 WHERE id = 1;
ERROR 1451 (23000) : Cannot DELETE OR UPDATE a parent ROW : a FOREIGN KEY CONSTRAINT fails ( `tts`.`student_score`, CONSTRAINT `s_id` FOREIGN KEY (`student_id`) REFERENCES `student` (`id`) )
3 更改事件觸發(fā)限制為 CASCADE
#刪除舊的外鍵 ALTER TABLE student_score DROP FOREIGN KEY s_id; #添加新的外鍵，修改事件觸發(fā)限制為 CASCADE ALTER TABLE student_score ADD CONSTRAINT s_id FOREIGN KEY (student_id) REFERENCES student (id) ON DELETE CASCADE ON UPDATE CASCADE;
（1）查看表結(jié)構(gòu)
CREATE TABLE `student_score` ( `id` int(11) NOT NULL AUTO_INCREMENT, `class` char(255) DEFAULT NULL, `score` char(255) DEFAULT NULL, `student_id` int(11) DEFAULT NULL, PRIMARY KEY (`id`), KEY `s_id` (`student_id`), CONSTRAINT `s_id` FOREIGN KEY (`student_id`) REFERENCES `student` (`id`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;
（2）查看此時兩表中的數(shù)據(jù)
（3）此時，當(dāng)主表修改主鍵值，從表中相關(guān)行的外鍵值將一起修改
UPDATE student SET id = 2 WHERE id = 1;
（4）如果主表刪除行，從表中的相關(guān)行將一起被刪除
DELETE FROM student WHERE id = 2;
4 結(jié)論
事件觸發(fā)限制條件的不同，會造成兩張表中的操作限制不同，其他幾個限制條件相對好理解，大家可以自己進行嘗試，體會其中的區(qū)別。
四、刪除外鍵約束
ALTER TABLE 表名 DROP FOREIGN KEY 外鍵名; ALTER TABLE student_score DROP FOREIGN KEY s_id;
總結(jié)
到此這篇關(guān)于MySQL數(shù)據(jù)庫中外鍵(foreign key)的文章就介紹到這了,更多相關(guān)MySQL外鍵(foreign key)內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！
相關(guān)文章
本文系統(tǒng)介紹了MySQL常用命令,涵蓋數(shù)據(jù)庫管理、數(shù)據(jù)操作、備份恢復(fù)及查詢優(yōu)化,包括啟停服務(wù)、連接操作、表結(jié)構(gòu)管理、數(shù)據(jù)增刪改查、文件導(dǎo)入導(dǎo)出和性能分析等實用腳本示例2025-07-07
MySQL 自定義函數(shù)CREATE FUNCTION示例
本節(jié)主要介紹了MySQL 自定義函數(shù)CREATE FUNCTION，下面是示例代碼，需要的朋友可以參考下2014-07-07
MySQL優(yōu)化GROUP BY（松散索引掃描與緊湊索引掃描）
這篇文章主要介紹了MySQL優(yōu)化GROUP BY（松散索引掃描與緊湊索引掃描）,需要的朋友可以參考下2016-05-05
MySQL中的GROUP BY子句用于將查詢結(jié)果按一個或多個列進行分組,通常與聚合函數(shù),實現(xiàn)數(shù)據(jù)的分組統(tǒng)計,本文給大家介紹mysql的group by函數(shù)使用方法,感興趣的朋友跟隨小編一起看看吧2025-08-08
在現(xiàn)代應(yīng)用中,數(shù)據(jù)庫是核心組件之一,無論是個人項目還是企業(yè)級應(yīng)用,數(shù)據(jù)的安全性和完整性都至關(guān)重要,為了防止數(shù)據(jù)丟失、損壞或意外刪除,定期備份數(shù)據(jù)庫是必不可少的,本文將詳細(xì)介紹 MySQL 數(shù)據(jù)庫的備份與恢復(fù)方法,需要的朋友可以參考下2024-10-10
如何修改Linux服務(wù)器中的MySQL數(shù)據(jù)庫密碼
這篇文章主要介紹了如何修改Linux服務(wù)器中的MySQL數(shù)據(jù)庫密碼問題，具有很好的參考價值，希望對大家有所幫助。如有錯誤或未考慮完全的地方，望不吝賜教2023-06-06
MySQL數(shù)據(jù)庫改名的詳細(xì)方法教程
在很多人看來Oracle數(shù)據(jù)庫一旦建好后,數(shù)據(jù)庫的名字就不能改變了,其實不然,下面這篇文章主要給大家介紹了關(guān)于MySQL數(shù)據(jù)庫改名的相關(guān)資料,需要的朋友可以參考下2023-03-03
關(guān)于mysql中innodb的count優(yōu)化問題分享
關(guān)于mysql中innodb的count優(yōu)化問題，有需要的朋友可以參考下2013-02-02
IDEA無法連接mysql數(shù)據(jù)庫的6種解決方法大全
這篇文章主要介紹了IDEA無法連接mysql數(shù)據(jù)庫的6種解決方法大全，文中通過示例代碼介紹的非常詳細(xì)，對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價值，需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧2020-11-11
MySQL使用innobackupex備份連接服務(wù)器失敗的解決方法
這篇文章主要為大家詳細(xì)介紹了MySQL使用innobackupex備份連接服務(wù)器失敗的解決方法，具有一定的參考價值，感興趣的小伙伴們可以參考一下2017-02-02