MySQL — 外鍵關聯操作
目錄
文章目錄
目錄
Mysql 的外鍵約束
創建表時定義外鍵（References，參照）
修改原有表的外鍵約束
刪除外鍵約束
參考文檔
Mysql 的外鍵約束
注意，MySQL 的 InnoDB 表引擎才支持外鍵關聯，MyISAM 不支持。MySQL 還支持手動打開或關閉外鍵約束：SET FOREIGN_KEY_CHECKS = 0/1;。
使用外鍵約束最大的好處在于 MySQL 會幫助我們完成數據的一致性檢查。當我們使用默認的外鍵類型 RESTRICT 時，在創建、修改或者刪除記錄時都會檢查引用的合法性。
假設我們的數據庫中包含 posts(id, author_id, content) 和 authors(id, name) 兩張表，在執行如下所示的操作時都會觸發數據庫對外鍵的檢查：
向 posts 表中插入數據時，檢查 author_id 是否在 authors 表中存在；
修改 posts 表中的數據時，檢查 author_id 是否在 authors 表中存在；
刪除 authors 表中的數據時，檢查 posts 中是否存在引用當前記錄的外鍵；
作為專門用于管理數據的系統，數據庫與應用服務相比能夠更好地保證完整性，而上述的這些操作都是引入外鍵帶來的額外工作，不過這也是數據庫保證數據完整性的必要代價。上述的這些分析都是理論上的定性分析，我們其實可以簡單的定量分析一下引入外鍵對性能的影響。
創建表時定義外鍵（References，參照）
在 CREATE TABLE 語句中，通過 FOREIGN KEY 關鍵字來指定外鍵，具體的語法格式如下：
[CONSTRAINT <外鍵名>] FOREIGN KEY 字段名 [，字段名2，…] REFERENCES <主表名> 主鍵列1 [，主鍵列2，…]
1
示例：
# 部門表 tb_dept1（主表） CREATE TABLE tb_dept1 ( id INT(11) PRIMARY KEY, name VARCHAR(22) NOT NULL, location VARCHAR(50) ) ENGINE=InnoDB DEFAULT CHARSET=gb2312; # 員工表 tb_emp6（從表），創建外鍵約束，讓 deptId 作為外鍵關聯到 tb_dept1 的主鍵 id。 CREATE TABLE tb_emp6 ( id INT(11) PRIMARY KEY, name VARCHAR(25), deptId INT(11), salary FLOAT, CONSTRAINT fk_emp_dept1 FOREIGN KEY(deptId) REFERENCES tb_dept1(id) ) ENGINE=InnoDB DEFAULT CHARSET=gb2312;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
NOTE：從表的外鍵關聯的必須是主表的主鍵，且主鍵和外鍵的數據類型必須一致。
以上語句執行成功之后，在表 tb_emp6 上添加了名稱為 fk_emp_dept1 的外鍵約束，外鍵名稱為 deptId，其依賴于表 tb_dept1 的主鍵 id。
查看主表的約束信息：
MariaDB [test_db]> select * from INFORMATION_SCHEMA.KEY_COLUMN_USAGE where REFERENCED_TABLE_NAME='tb_dept1'\G; *************************** 1. row *************************** CONSTRAINT_CATALOG: def CONSTRAINT_SCHEMA: test_db CONSTRAINT_NAME: fk_emp_dept1 TABLE_CATALOG: def TABLE_SCHEMA: test_db TABLE_NAME: tb_emp6 COLUMN_NAME: deptId ORDINAL_POSITION: 1 POSITION_IN_UNIQUE_CONSTRAINT: 1 REFERENCED_TABLE_SCHEMA: test_db REFERENCED_TABLE_NAME: tb_dept1 REFERENCED_COLUMN_NAME: id 1 row in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
修改原有表的外鍵約束
外鍵約束也可以在修改表時添加，但是添加外鍵約束的前提是：從表中外鍵列中的數據必須與主表中主鍵列中的數據一致或者是沒有數據。
在修改數據表時添加外鍵約束的語法格式如下：
ALTER TABLE <數據表名> ADD CONSTRAINT <外鍵名> FOREIGN KEY(<列名>) REFERENCES <主表名> (<列名>);
1
示例：修改數據表 tb_emp2，將字段 deptId 設置為外鍵，與數據表 tb_dept1 的主鍵 id 進行關聯。
# 創建 tb_emp2（從表） CREATE TABLE tb_emp2 ( id INT(11) PRIMARY KEY, name VARCHAR(25), deptId INT(11), salary FLOAT ) ENGINE=InnoDB DEFAULT CHARSET=gb2312; MariaDB [test_db]> desc tb_emp2; +--------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------+-------------+------+-----+---------+-------+ | id | int(11) | NO | PRI | NULL | | | name | varchar(25) | YES | | NULL | | | deptId | int(11) | YES | | NULL | | | salary | float | YES | | NULL | | +--------+-------------+------+-----+---------+-------+ # 添加外鍵約束 ALTER TABLE tb_emp2 ADD CONSTRAINT fk_tb_dept1 FOREIGN KEY(deptId) REFERENCES tb_dept1(id); MariaDB [test_db]> desc tb_emp2; +--------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------+-------------+------+-----+---------+-------+ | id | int(11) | NO | PRI | NULL | | | name | varchar(25) | YES | | NULL | | | deptId | int(11) | YES | MUL | NULL | | | salary | float | YES | | NULL | | +--------+-------------+------+-----+---------+-------+ MariaDB [test_db]> SHOW CREATE TABLE tb_emp2\G *************************** 1. row *************************** Table: tb_emp2 Create Table: CREATE TABLE `tb_emp2` ( `id` int(11) NOT NULL, `name` varchar(25) DEFAULT NULL, `deptId` int(11) DEFAULT NULL, `salary` float DEFAULT NULL, PRIMARY KEY (`id`), KEY `fk_tb_dept1` (`deptId`), CONSTRAINT `fk_tb_dept1` FOREIGN KEY (`deptId`) REFERENCES `tb_dept1` (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=gb2312
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
刪除外鍵約束
當一個表中不需要外鍵約束時，就需要從表中將其刪除。外鍵一旦刪除，就會解除主表和從表間的關聯關系。
刪除外鍵約束的語法格式如下所示：
ALTER TABLE <表名> DROP FOREIGN KEY <外鍵約束名>;
1
示例：刪除數據表 tb_emp2 中的外鍵約束 fk_tb_dept1。
ALTER TABLE tb_emp2 DROP FOREIGN KEY fk_tb_dept1; MariaDB [test_db]> SHOW CREATE TABLE tb_emp2\G *************************** 1. row *************************** Table: tb_emp2 Create Table: CREATE TABLE `tb_emp2` ( `id` int(11) NOT NULL, `name` varchar(25) DEFAULT NULL, `deptId` int(11) DEFAULT NULL, `salary` float DEFAULT NULL, PRIMARY KEY (`id`), KEY `fk_tb_dept1` (`deptId`) ) ENGINE=InnoDB DEFAULT CHARSET=gb2312
1
2
3
4
5
6
7
8
9
10
11
12
13
參考文檔
http://c.biancheng.net/view/2441.html
MySQL 數據庫
版權聲明：本文內容由網絡用戶投稿，版權歸原作者所有，本站不擁有其著作權，亦不承擔相應法律責任。如果您發現本站中有涉嫌抄襲或描述失實的內容，請聯系我們jiasou666@gmail.com 處理，核實后本網站將在24小時內刪除侵權內容。
版權聲明：本文內容由網絡用戶投稿，版權歸原作者所有，本站不擁有其著作權，亦不承擔相應法律責任。如果您發現本站中有涉嫌抄襲或描述失實的內容，請聯系我們jiasou666@gmail.com 處理，核實后本網站將在24小時內刪除侵權內容。
相關文章