當(dāng)前位置：首頁 > 编程资源 > 编程问答 >内容正文

编程问答

MySql外键学习总结

發(fā)布時(shí)間：2025/4/14 编程问答 12 豆豆

生活随笔收集整理的這篇文章主要介紹了 MySql外键学习总结小編覺得挺不錯(cuò)的,現(xiàn)在分享給大家,幫大家做個(gè)參考.

mysql添加外鍵

為已經(jīng)添加好的數(shù)據(jù)表添加外鍵:
語法:alter table 表名 add constraint FK_ID foreign key(你的外鍵字段名) REFERENCES 外表表名(

對(duì)應(yīng)的表的主鍵字段名);
例: alter table tb_active add constraint FK_ID foreign key(user_id) REFERENCES tb_user(id)
//FK_ID是外鍵的名稱
/*
CREATE TABLE `tb_active` (
?`id` int(11) NOT NULL AUTO_INCREMENT,
?`title` varchar(100) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
?`content` text CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
?`user_id` int(11) NOT NULL,
?PRIMARY KEY (`id`),
?KEY `user_id` (`user_id`),
?KEY `user_id_2` (`user_id`),
?CONSTRAINT `FK_ID` FOREIGN KEY (`user_id`) REFERENCES `tb_user` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
*/
?
刪除外鍵
語法： ALTER TABLE table-name DROP FOREIGN KEY key-id;
例： ? ALTER TABLE `tb_active` DROP FOREIGN KEY `FK_ID`
?
自動(dòng)鍵更新和刪除：
外鍵可以保證新插入的記錄的完整性，但是，如果在REFERENCES從句中已命名的表刪除記錄會(huì)怎么樣？

在使用同樣的值作為外鍵的輔助表中會(huì)發(fā)生什么?
??
?很明顯，那些記錄也應(yīng)該被刪除，否則在數(shù)據(jù)庫(kù)中就會(huì)有很多無意義的孤立記錄，MYSQL可以通過向

FOREIGN KEY...REFERENCES修飾符添加一個(gè)ON DELETE 或ON UPDATE子句簡(jiǎn)化任務(wù)，它告訴了數(shù)據(jù)庫(kù)在這

種情況如何處理孤立任務(wù)
?
?關(guān)鍵字 ? ? 含義
?CASCADE ? ?刪除包含與已刪除鍵值有參照關(guān)系的所有記錄
?SET NULL ? 修改包含與已刪除鍵值有參照關(guān)系的所有記錄，使用NULL值替換（只能用于已標(biāo)記為NOT?

NULL的字段）
?RESTRICT ? 拒絕刪除要求，直到使用刪除鍵值的輔助表被手工刪除，并且沒有參照時(shí)(這是默認(rèn)設(shè)置，

也是最安全的設(shè)置)
?NO ACTION ?啥也不做
?
?請(qǐng)注意，通過ON UPDATE 和 ON DELETE規(guī)則，設(shè)置MYSQL能夠?qū)崿F(xiàn)自動(dòng)操作時(shí)，如果鍵的關(guān)系沒有設(shè)置

好，可能會(huì)導(dǎo)致嚴(yán)重的數(shù)據(jù)破壞，
?例如：如果一系列的表通過外鍵關(guān)系和ON DELETE CASCADE 規(guī)則連接時(shí)，任意一個(gè)主表的變化都會(huì)導(dǎo)致

甚至只和原始刪除有一些將要聯(lián)系的記錄在沒有警告的情況被刪除，所以，我們?cè)诓僮髦斑€要檢查這

些規(guī)則的，操作之后還要再次檢查.
?
添加外鍵
alter table locstock add foreign key locstock_ibfk2(stockid) references product(stockid)
locstock 為表名, locstock_ibfk2 為外鍵名第一個(gè)括號(hào)里填寫外鍵列名, product為表名,第二個(gè)括號(hào)

里是寫外鍵關(guān)聯(lián)的列名
?
刪除外鍵?
alter table locstock drop foreign key locstock_ibfk2
?
查看表有哪些外鍵
show create table locstock
?
[CONSTRAINT symbol] FOREIGN KEY [id] (index_col_name, ...) ?
? ? REFERENCES tbl_name (index_col_name, ...) ?
? ? [ON DELETE {RESTRICT | CASCADE | SET NULL | NO ACTION}] ?
? ? [ON UPDATE {RESTRICT | CASCADE | SET NULL | NO ACTION}] ?
?
? ? ? ? ?所有tables必須是InnoDB型，它們不能是臨時(shí)表。
· ? ? ? ? 在引用表中，必須有一個(gè)索引，外鍵列以同樣的順序被列在其中作為第一列。這樣一個(gè)索引

如果不存在，它必須在引用表里被自動(dòng)創(chuàng)建。
· ? ? ? ? 在引用表中，必須有一個(gè)索引，被引用的列以同樣的順序被列在其中作為第一列。
· ? ? ? ? 不支持對(duì)外鍵列的索引前綴。這樣的后果之一是BLOB和TEXT列不被包括在一個(gè)外鍵中，這

是因?yàn)閷?duì)這些列的索引必須總是包含一個(gè)前綴長(zhǎng)度。
· ? ? ? ? 如果CONSTRAINTsymbol 被給出，它在數(shù)據(jù)庫(kù)里必須是唯一的。如果它沒有被給出，InnoDB

自動(dòng)創(chuàng)建這個(gè)名字。
========

MySQL中的外鍵是什么、有什么作用

我的疑問是 "使用外鍵約束" ，然后我對(duì) "外鍵" 這個(gè)詞不是很理解，查詢相關(guān)資料都是講一些術(shù)語，

說外鍵的主要作用是：保持?jǐn)?shù)據(jù)的一致性、完整性。聽得我是一頭霧水。
關(guān)于外鍵，我有自己的一些理解，但是不曉得是否正確，舉個(gè)例子來表達(dá)我的看法：假如現(xiàn)在需要建立

一個(gè)表，一個(gè)什么樣的表呢？一個(gè)班級(jí)的學(xué)生個(gè)人信息表：

所以在設(shè)計(jì)的時(shí)候，就給表1添加一個(gè)外鍵，這個(gè)外鍵就是表2中的學(xué)號(hào)字段，那么這樣表1就是主表，表

2就是子表。所以結(jié)合2張表就能保持?jǐn)?shù)據(jù)的一致性、完整性（估計(jì)就是還原成原來的那張大表吧）。
借著這個(gè)例子再談?wù)勍怄I的一些事項(xiàng)：
1、表1可以有一個(gè)或者多個(gè)外鍵，也可以沒有。（如果表1有多個(gè)外鍵可不可以是這樣的情況，表2中的

多個(gè)字段是表1的外鍵；或者說表1的多個(gè)外鍵是在多個(gè)表中）
2、這個(gè)外鍵可以不是表1的主鍵，但必須是子表的主鍵。（簡(jiǎn)單的說就是，如果一個(gè)字段是某個(gè)表的外

鍵時(shí)，那么該字段必須是主鍵）
以上就是我個(gè)人對(duì)外鍵的理解。

----------------------------------------解---答---糾---

正-----------------------------------------

什么是外鍵
+-------+ ref +-------+
| sub | ------> | main |
+-------+ +-------+

從表（sub）的某列引用（ref）主表（main）的某列的值。比如學(xué)生表有個(gè)學(xué)生編號(hào)（sid），分?jǐn)?shù)表中

的學(xué)生列（stu）引用學(xué)生表的學(xué) 生編號(hào)，此時(shí)對(duì)于分?jǐn)?shù)表的 stu 來說，學(xué)生表的 sid 就是外鍵。從

表也叫外鍵表，主表也叫主鍵表、外表，列也叫字段。

所以在設(shè)計(jì)的時(shí)候，就給表1添加一個(gè)外鍵，這個(gè)外鍵就是表2中的學(xué)號(hào)字段，那么這樣表1就是主表，表

2就是子表。

你的主從關(guān)系理解顛倒了。你的圖中，表1的確是主表，表2是子表，但不是叫做給表1添加一個(gè)外鍵，而

是給表2添加一個(gè)外鍵，表2中的學(xué)號(hào) 字段就叫外鍵，它是表1學(xué)號(hào)字段的主鍵。你可以這樣說：表1的學(xué)

號(hào)字段是表2的外鍵。
外鍵用來干什么

你貼的圖片已經(jīng)解釋了。為了一張表記錄的數(shù)據(jù)不要太過冗余。這和軟件工程的模塊化思想差不多類似

，只不過在數(shù)據(jù)庫(kù)中是對(duì)表關(guān)系進(jìn)行解耦，盡量讓表記錄的數(shù)據(jù)單一化。就如你貼的圖片中，把成績(jī)和

學(xué)生信息放在一張表中就太冗余了，成績(jī)完全可以以學(xué)生的id作為區(qū)分標(biāo)識(shí)。
為什么說外鍵能保持?jǐn)?shù)據(jù)的一致性、完整性
你想想，你的圖中的第一章表分割成了表1和表2，表2的學(xué)號(hào)引用了表1的學(xué)號(hào)字段作為外鍵，如果不建

立外鍵，只是和表1一樣單純性地設(shè)立一個(gè)學(xué)號(hào)字段，那么和建立外鍵有什么區(qū)別呢？

比如表1中張三的學(xué)號(hào)為20140900001，那么我在表2中插數(shù)據(jù)的時(shí)候在學(xué)號(hào)字段插20140900001來記錄張

三的成績(jī)不也是做到了表的解耦了嗎？

這里存在的問題是，在不設(shè)置外鍵的情況下，表2的學(xué)號(hào)字段和表1的學(xué)號(hào)字段是沒有關(guān)聯(lián)的。只是你自

己認(rèn)為他們有關(guān)系而已，數(shù)據(jù)庫(kù)并不認(rèn)為它倆有關(guān)系。也就是說，你在表2的學(xué)號(hào)字段插了一個(gè)值（比

如20140999999），但是這個(gè)值在表1中并沒有，這個(gè)時(shí)候，數(shù)據(jù)庫(kù)還是允許你插入的，它并不會(huì)對(duì)插入

的數(shù)據(jù)做關(guān)系檢查。然而在設(shè)置外鍵的情況下，你插入表2學(xué)號(hào)字段的值必須要求在表1的學(xué)號(hào)字段能找

到。同時(shí)，如果你要?jiǎng)h除表1的某個(gè)學(xué)號(hào)字段，必須保證表2中沒有引用該字段值的列，否則就沒法刪除

。這就是所謂的保持?jǐn)?shù)據(jù)的一致性和完整性。你想，如果表2還引用表1的某個(gè)學(xué)號(hào)，你卻把表1中的這

個(gè)學(xué)號(hào)刪了，表2就不知道這個(gè)學(xué)號(hào)對(duì)應(yīng)的學(xué)生是哪個(gè)學(xué)生。數(shù)據(jù)的一致性還包括數(shù)據(jù)類型的一致性（這?

個(gè)見下面就知道了）。
外鍵的使用規(guī)則

從表的字段必須與外鍵類型相同（如上，分?jǐn)?shù)表 stu 的類型必須和學(xué)生表 sid 的類型相同，比如都是?

int(10) 類型）外鍵必須是主表的唯一鍵（如上，學(xué)生表 sid 是主鍵，而主鍵是唯一的，所以可以作為

分?jǐn)?shù)表 stu 的外鍵）有關(guān)聯(lián)的字段（如上，分?jǐn)?shù)表之所以使用學(xué)生表的 sid 是因?yàn)閮烧哂嘘P(guān)聯(lián)，分?jǐn)?shù)

表記錄的是學(xué)生的分?jǐn)?shù)，而學(xué)生可以用 sid 來唯一標(biāo)識(shí)）避免使用復(fù)合鍵（也就是說從表可以同時(shí)引

用多個(gè)外表的字段作為一個(gè)外鍵，一般不推薦這種做法）
你的問題
如果表1有多個(gè)外鍵可不可以是這樣的情況，表2中的多個(gè)字段是表1的外鍵；或者說表1的多個(gè)外鍵是在

多個(gè)表中。
都可以。因?yàn)楸?的外鍵不一定是表2的主鍵，也可以是唯一鍵（UNIQUE）。比如表2有個(gè)主鍵 A，有個(gè)唯

一鍵 B，表1兩個(gè)字段 A' 和 B’ 分別引用表2的 A 和 B，這就是多對(duì)多的關(guān)系了。再或者表2主鍵 A，

表3主鍵 B，表1的兩個(gè)字段 A' 和 B' 分別引用表2的 A 和表3 的 B。
這個(gè)外鍵可以不是表1的主鍵，但必須是子表的主鍵。（簡(jiǎn)單的說就是，如果一個(gè)字段是某個(gè)表的外鍵時(shí)

，那么該字段必須是主鍵）
因?yàn)槟闱懊婢屠斫忮e(cuò)了，所以這句話本身就是錯(cuò)的。對(duì)于從表來說，外鍵不一定需要作為從表的主鍵，

外鍵也不一定是外表的主鍵，外表的唯一鍵就可以作為從表的外鍵。

再給一張圖以幫助理解
========

mysql外鍵(FOREIGN KEY)的簡(jiǎn)單使用

一、基本概念
1、MySQL中“鍵”和“索引”的定義相同，所以外鍵和主鍵一樣也是索引的一種。不同的是MySQL會(huì)自動(dòng)

為所有表的主鍵進(jìn)行索引，但是外鍵字段必須由用戶進(jìn)行明確的索引。用于外鍵關(guān)系的字段必須在所有

的參照表中進(jìn)行明確地索引，InnoDB不能自動(dòng)地創(chuàng)建索引。
2、外鍵可以是一對(duì)一的，一個(gè)表的記錄只能與另一個(gè)表的一條記錄連接，或者是一對(duì)多的，一個(gè)表的記

錄與另一個(gè)表的多條記錄連接。
3、如果需要更好的性能，并且不需要完整性檢查，可以選擇使用MyISAM表類型，如果想要在MySQL中根

據(jù)參照完整性來建立表并且希望在此基礎(chǔ)上保持良好的性能，最好選擇表結(jié)構(gòu)為innoDB類型。
4、外鍵的使用條件
① 兩個(gè)表必須是InnoDB表，MyISAM表暫時(shí)不支持外鍵
② 外鍵列必須建立了索引，MySQL 4.1.2以后的版本在建立外鍵時(shí)會(huì)自動(dòng)創(chuàng)建索引，但如果在較早的版

本則需要顯式建立；
③ 外鍵關(guān)系的兩個(gè)表的列必須是數(shù)據(jù)類型相似，也就是可以相互轉(zhuǎn)換類型的列，比如int和tinyint可以

，而int和char則不可以；
5、外鍵的好處：可以使得兩張表關(guān)聯(lián)，保證數(shù)據(jù)的一致性和實(shí)現(xiàn)一些級(jí)聯(lián)操作。
二、使用方法
1、創(chuàng)建外鍵的語法：
外鍵的定義語法：
[CONSTRAINT symbol] FOREIGN KEY [id] (index_col_name, ...)
? ? REFERENCES tbl_name (index_col_name, ...)
? ? [ON DELETE {RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT}]
? ? [ON UPDATE {RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT}]
? ? 該語法可以在 CREATE TABLE 和 ALTER TABLE 時(shí)使用，如果不指定CONSTRAINT symbol，MYSQL會(huì)自

動(dòng)生成一個(gè)名字。
ON DELETE、ON UPDATE表示事件觸發(fā)限制，可設(shè)參數(shù)：
① RESTRICT（限制外表中的外鍵改動(dòng)，默認(rèn)值）
② CASCADE（跟隨外鍵改動(dòng)）
③ SET NULL（設(shè)空值）
④ SET DEFAULT（設(shè)默認(rèn)值）
⑤ NO ACTION（無動(dòng)作，默認(rèn)的）
2、示例
1）創(chuàng)建表1
create table repo_table(
repo_id char(13) not null primary key,
repo_name char(14) not null)
type=innodb;
創(chuàng)建表2
mysql> create table busi_table(
? ? -> busi_id char(13) not null primary key,
? ? -> busi_name char(13) not null,
? ? -> repo_id char(13) not null,
? ? -> foreign key(repo_id) references repo_table(repo_id))
-> type=innodb;
2）插入數(shù)據(jù)
insert into repo_table values("12","sz"); //success
insert into repo_table values("13","cd"); //success
insert into busi_table values("1003","cd", "13"); //success
insert into busi_table values("1002","sz", "12"); //success
insert into busi_table values("1001","gx", "11"); //failed,提示：
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails?

(`smb_man`.`busi_table`, CONSTRAINT `busi_table_ibfk_1` FOREIGN KEY (`repo_id`) REFERENCES?

`repo_table` (`repo_id`))
3）增加級(jí)聯(lián)操作
mysql> alter table busi_table
? ? -> add constraint id_check
? ? -> foreign key(repo_id)
? ? -> references repo_table(repo_id)
? ? -> on delete cascade
? ? -> on update cascade;
-----
ENGINE=InnoDB DEFAULT CHARSET=gb2312； //另一種方法，可以替換type=innodb;
3、相關(guān)操作
外鍵約束（表2）對(duì)父表（表1）的含義:
? ? 在父表上進(jìn)行update/delete以更新或刪除在子表中有一條或多條對(duì)應(yīng)匹配行的候選鍵時(shí)，父表的行

為取決于：在定義子表的外鍵時(shí)指定的on update/on delete子句。
關(guān)鍵字
含義
CASCADE
刪除包含與已刪除鍵值有參照關(guān)系的所有記錄
SET NULL
修改包含與已刪除鍵值有參照關(guān)系的所有記錄，使用NULL值替換(只能用于已標(biāo)記為NOT NULL的字段)
RESTRICT
拒絕刪除要求，直到使用刪除鍵值的輔助表被手工刪除，并且沒有參照時(shí)(這是默認(rèn)設(shè)置，也是最安全的

設(shè)置)
NO ACTION
啥也不做
4、其他
在外鍵上建立索引:
index repo_id (repo_id),
foreign key(repo_id) references repo_table(repo_id))
========

鏈接

【1】 w3school關(guān)于mysql的專題講解
http://www.w3school.com.cn/sql/sql_foreignkey.asp
【2】 MySQL C API programming tutorial
http://zetcode.com/tutorials/mysqlcapitutorial/
【3】對(duì)外鍵的使用示例，很不錯(cuò)
http://hi.baidu.com/wangzhiqing999/blog/item/08761705954e18c4267fb523.html
http://www.cppblog.com/wolf/articles/69089.html
【4】 mysql官網(wǎng)英文網(wǎng)站
http://dev.mysql.com/doc/refman/5.5/en/innodb-foreign-key-constraints.html
中文網(wǎng)站
http://dev.mysql.com/doc/refman/5.1/zh/sql-syntax.html
【5】對(duì)外鍵講解的比較全面，可以一讀
http://www.xiaoxiaozi.com/2009/07/12/1158/
http://feidaodalian.iteye.com/blog/550179

總結(jié)

以上是生活随笔為你收集整理的MySql外键学习总结的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。

MySQL