MySQL數(shù)據(jù)庫中外鍵(foreign?key)用法詳解
更新時間：2023年10月27日 08:49:32 作者：知其黑、受其白
這篇文章主要給大家介紹了關(guān)于MySQL數(shù)據(jù)庫中外鍵(foreign?key)的相關(guān)資料,MySQL 外鍵約束可以用來保證表與表之間的關(guān)系完整性,文中通過代碼介紹的非常詳細,需要的朋友可以參考下
前言
在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ù)庫中，或者是當前正在創(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 | 限制外表中的外鍵改動（默認值，也是最安全的設置） |
| CASCADE | 跟隨外鍵改動 |
| SET NULL | 設為null值 |
| NO ACTION | 無動作 |
| SET DEFAULT | 設為默認值 |
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ù)學', '99', 1),
('英語', '98', 1);
數(shù)據(jù)插入正常，無報錯、警告信息。
2 觸發(fā)限制使用默認值 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）此時，當主表修改主鍵值，從表中相關(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)文章
這里是101條調(diào)節(jié)和優(yōu)化 MySQL安裝的技巧。一些技巧是針對特定的安裝環(huán)境的，但這些思路是通用的。我已經(jīng)把他們分成幾類，來幫助你掌握更多MySQL的調(diào)節(jié)和優(yōu)化技巧2013-08-08
這篇文章主要給大家介紹了關(guān)于mysql中的一些稍微復雜用法的相關(guān)資料，文中通過示例代碼介紹的非常詳細，對大家學習或者使用mysql具有一定的參考學習價值，需要的朋友們下面隨著小編來一起學習學習吧2018-11-11
Mysql自動備份與還原方法，需要的朋友可以參考下。2011-06-06
MySQL使用binlog日志恢復數(shù)據(jù)的方法步驟
binlog日志是用于記錄所有修改數(shù)據(jù)庫內(nèi)容的操作,本文主要介紹了MySQL使用binlog日志恢復數(shù)據(jù)的方法步驟,具有一定的參考價值,感興趣的可以了解一下2025-03-03
遠程訪問MySQL數(shù)據(jù)庫的方法小結(jié)
MySQL數(shù)據(jù)庫不允許從遠程訪問如何辦?本文提供了 3種解決思路方法2009-12-12
python中的mysql數(shù)據(jù)庫LIKE操作符詳解
LIKE操作符用于在WHERE子句中搜索列中的指定模式，like操作符的語法在文章開頭也給大家提到，通過兩種示例代碼給大家介紹python中的mysql數(shù)據(jù)庫LIKE操作符知識，感興趣的朋友跟隨小編一起看看吧2021-07-07
這篇文章主要介紹了MySQL 事務autocommit自動提交操作，具有很好的參考價值，希望對大家有所幫助。一起跟隨小編過來看看吧2021-01-01
在很多地方都使用到了mysql的日期查詢,下面這篇文章主要給大家介紹了關(guān)于mysql時間區(qū)間問題的相關(guān)資料,文中通過實例代碼介紹的非常詳細,需要的朋友可以參考下2022-04-04
這篇文章主要介紹了mysql啟動服務報1058錯誤的解決方法,需要的朋友可以參考下2014-03-03
MYSQL ON UPDATE CURRENT_TIMESTAMP當字段值發(fā)生改變時才會更
本文主要介紹了MYSQL ON UPDATE CURRENT_TIMESTAMP當字段值發(fā)生改變時才會更新記錄的時間，文中通過示例代碼介紹的非常詳細，對大家的學習或者工作具有一定的參考學習價值，需要的朋友們下面隨著小編來一起學習學習吧2023-01-01