關系型數據庫的外鍵約束與關聯

目錄

文章目錄

目錄

外鍵約束

外鍵關聯

外鍵的作用

外鍵的性能問題

是否使用外鍵？

使用外鍵的守則

互聯網應用應該盡量避免使用外鍵

在業務邏輯中模擬數據庫外鍵

on delete/update 的外鍵關聯操作類型

CASCADE（級聯）約束方式

SET NULL（設空）約束方式

以 NO ACTION（不采取行動）或 RESTRICT（禁止）方式

外鍵約束

外鍵約束（Foreign key）是關系型數據庫中的 Table 的一個特殊字段，經常與主鍵約束（Primary key）一起使用。對于兩個具有關聯關系的表而言，相關聯字段中主鍵所在的表就是主表（父表），外鍵所在的表就是從表（子表）。外鍵約束可以保證引用的完整性（Referential Integrity）。

引用完整性是數據的屬性，如果數據擁有該屬性，那么數據中所有的引用都是合法的，在關系型數據庫的上下文中，這就意味著關系型數據庫中引用另一個表中的值必須存在。

簡而言之，外鍵約束就是用來建立主表與從表的關聯關系，為兩個表的數據建立連接，約束兩個表中數據的一致性和完整性。

NOTE：一個 Table 可以有一個或多個外鍵，外鍵可以為空值，若不為空值，則每一個外鍵的值必須等于主表中主鍵的某個值。

外鍵關聯

所謂外鍵關聯，即：B 存在外鍵 b_f_k，以 A 表的 a_k 作為參照（References）列，則 A 為主表，B 為從表。

若 A、B 關聯了 on delete/update 等操作，則 A 中某記錄的更新或刪除會聯動著 B 中外鍵與其關聯對應的記錄做更新或刪除操作。

反之，B 怎樣變 A 不必跟隨變動，且 A 中必須事先存在 B 要插入的數據外鍵列的值，例如：B.bfk 作為外鍵參照 A.ak，則 B.bfk 插入的值必須是 A.ak 中已存在的。簡而言之，就是若 B 有以 A 作為參照的外鍵，則 B 中的此字段的取值只能是 A 中存在的值。

外鍵的作用

外鍵用于支持關系型數據庫的 “參照完整性”，外鍵具有保持數據完整性和一致性的機制，對業務處理有著很好的校驗作用。

舉例說明：假設 Table user 的 Column user.id 為主鍵（Primary key），Table profile 的 Column profile.uid 為主鍵。以 user 為主表、profile 為關聯表、profile.uid 為外鍵（Foreign key）并將 user.id 作為參考（References），且聯動了刪除/更新操作（on delete/update cascade）。那么：

在 user 中刪除 id 為 1 的記錄，會聯動刪除 profile 中 uid 為 1 的記錄。

在 user 中更新 id 為 1 的記錄至 id 為 2，則 profile 中 uid 為 1 的記錄也會被聯動更新至 uid 為 2。

這樣即保持了數據的完整性，也保證了數據的一致性。而且這個工作都是交由 RDBMS 內部實現的觸發器來完成的，不需要額外的編碼。

外鍵的性能問題

外鍵的使用往往會帶來性能問題，因為：

數據庫需要維護外鍵的內部管理；

外鍵等于把數據的一致性事務實現，全部交給數據庫服務器完成；

涉及外鍵字段的增，刪，更新操作，需要觸發相關操作去檢查，而不得不消耗資源；

外鍵還會因為需要請求對其他表內部加鎖而容易出現死鎖情況。

是否使用外鍵？

因為外鍵具有性能問題，所以是否采用外鍵需要考慮業務應用場景，以及開發成本：

互聯網行業應用不推薦使用外鍵：用戶量大，并發度高。為此，數據庫服務器很容易成為高并發訪問的性能瓶頸，尤其受 I/O 能力限制，且不能輕易地水平擴展。此場景中，應該把數據一致性的實現放到業務邏輯中，讓應用服務器來承擔這部分的功能和壓力，因為應用服務器可以輕松做到水平伸縮；

傳統行業可以考慮使用外鍵：因為軟件應用的人數是有限且可控的，數據庫服務器的數據量也一般不會超大，且活躍數據有限。該場景中使用外鍵可以降低開發成本，借助 RDBMS 自身的觸發器可以實現實體表與關聯表之間的數據一致性和更新。另外，使用外鍵還可以做到開發人員和數據庫設計人員（DBA）的分工，DBA 可以為程序員承擔更多的工作量；

使用外鍵的守則

主表必須已經存在于數據庫中，或者是當前正在創建的表。如果是后一種情況，則主表與從表是同一個表，這樣的表稱為自參照表，這種結構稱為自參照完整性。

必須為主表定義主鍵。

外鍵中列的數目必須和主表的主鍵中列的數目相同。

外鍵中列的數據類型必須和主表主鍵中對應列的數據類型相同。

互聯網應用應該盡量避免使用外鍵

不使用外鍵的原因其實很簡單，因為 MySQL、PostgreSQL 等關系型數據庫很難水平擴容，但是無狀態的服務往往都可以很容易地擴容。由于外鍵等特性需要數據庫執行額外的工作，而這些操作會占用數據庫的計算資源，所以我們可以將大部分的需求都遷移到無狀態的服務中完成以降低數據庫的工作負載，從而避免數據庫成為高并發性能的瓶頸。

另外，級聯刪除的出發點是為了保證數據的完整性，但是在設計關系表之間的不同關系時，我們也需要注意級聯刪除引起的數據大規模刪除的問題。當客戶端想要在數據庫中刪除 authos 表中的數據時，如果我們同時在 authors 和 posts 中指定了級聯刪除的行為，那么數據庫會同時刪除所有關聯的 posts 記錄以及與 posts 表關聯的 comments 數據。

這種涉及多級的級聯刪除行為在數據量較小的數據庫中不會導致問題，但是在數據量較大的數據庫中刪除關鍵數據可能會引起雪崩，一條記錄的刪除可能會被放大到幾十倍甚至上百倍，這些對磁盤的隨機 I/O 會帶來巨大的開銷，是我們想要盡可能避免的情況。如果我們能夠較好地設計各個表之間的關系并且慎用 CASCADE 行為，這對于保證數據庫中數據的合法性有著很重要的意義，使用該特性可以避免數據庫中出現過期的、不合法的數據，但是在使用時也要合理預估可能造成的最壞情況。

在業務邏輯中模擬數據庫外鍵

想要在應用程序中模擬數據庫外鍵的功能其實比較容易，我們只需要遵循以下的幾個準則：

向表中插入數據或者修改表中的數據時，都應該執行額外的 SELECT 語句確保它引用的數據在數據庫中存在；

在刪除數據之前需要執行額外的 SELECT 語句檢查是否存在當前記錄的引用；

需要注意的是為了保證一致性，我們需要在事務中執行上述的查詢和修改語句，這樣才能完整模擬外鍵的功能。例如：當我們向 posts 表中插入或者修改數據時，需要的處理相對比較簡單，我們只需要執行有限的 SELECT 語句并按照如下所示的模式執行對應的操作就可以了：

BEGIN SELECT * FROM authors WHERE id = FOR UPDATE; -- INSERT INTO posts ... / UPDATE posts ... END

1

2

3

4

但是如果我們要刪除 authors 表中的數據，就需要查詢所有引用 authors 數據的表；如果有 10 個表都有指向 authors 表的外鍵，我們就需要在 10 個表中查詢是否存在對應的記錄，這個過程相對比較麻煩，不過也是為了實現完整性的必要代價，不過這種模擬外鍵方法其實遠比使用外鍵更消耗資源，它不僅需要查詢關聯數據，還要通過網絡發送更多的數據包。

手動實現數據庫的級聯刪除操作也是可行的，如果我們在一個事務中按照順序刪除所有的數據，確實可以保證數據的一致性，但是這與外鍵的級聯刪除功能沒有太大的區別，反而會有更差的表現。如果我們能夠接受在一個時間窗口內的數據不一致，就可以將一個大號的刪除任務拆成多個子任務分批執行，降低對數據庫影響的峰值。

DELETE FROM posts WHERE author_id = 1 LIMIT 100; DELETE FROM posts WHERE author_id = 1 LIMIT 100; ... DELETE FROM authors WHERE id = 1;

1

2

3

4

注意，與數據庫外鍵的 CASCADE 相比，這種方式會帶來更大的額外開銷，只是我們能降低對數據庫性能的瞬時影響。

on delete/update 的外鍵關聯操作類型

下文以 MySQL 為例。

no action（不采取行動）

cascade（級聯）

set null（設為空）

restrict（禁止）

示例：

# 主表 create table country ( id int not null, name varchar(30), primary key(id) ) ENGINE=InnoDB DEFAULT CHARSET=gb2312;

1

2

3

4

5

6

7

CASCADE（級聯）約束方式

CASCADE（級聯），表示主表在進行更新和刪除時，更新和刪除從表相對應的記錄。

# 從表 create table solider ( id int not null, name varchar(30), country_id int, primary key(id), foreign key(country_id) references country(id) on delete cascade on update cascade ) ENGINE=InnoDB DEFAULT CHARSET=gb2312;

1

2

3

4

5

6

7

8

9

參照完整性測試：

# 插入主表記錄 insert into country values(1, '西歐'); insert into country values(2, '瑪雅'); insert into country values(3, '西西里'); # 插入從表記錄 insert into solider values(1, '西歐見習步兵', 1); # 插入成功 insert into solider values(2, '瑪雅短矛兵', 2); # 插入成功 insert into solider values(3, '西西里諾曼騎士', 3); # 插入成功 insert into solider values(4, '法蘭西劍士', 4); # 插入失敗，因為主表中不存在 id 為 4 的記錄

1

2

3

4

5

6

7

8

9

10

約束方式測試：

insert into solider values(4, '瑪雅猛虎勇士', 2); # 成功插入 delete from country where id=2; # 會導致從表中 id 為 2 和 4 的記錄同時被刪除 update country set id=8 where id=1; # 會導致solider表中country_id為1的所有記錄同時也會被修改為8

1

2

3

SET NULL（設空）約束方式

SET NULL（設空），表示主表進行更新和刪除的時候，從表的對應字段被設為 NULL。

create table solider ( id int not null, name varchar(30), country_id int, primary key(id), foreign key(country_id) references country(id) on delete set null on update set null ) ENGINE=InnoDB DEFAULT CHARSET=gb2312;

1

2

3

4

5

6

7

8

參照完整性測試：

# 插入主表記錄 insert into country values(1, '西歐'); insert into country values(2, '瑪雅'); insert into country values(3, '西西里'); # 插入從表記錄 insert into solider values(1, '西歐見習步兵', 1); # 插入成功 insert into solider values(2, '瑪雅短矛兵', 2); # 插入成功 insert into solider values(3, '西西里諾曼騎士', 3); # 插入成功 insert into solider values(4, '法蘭西劍士',4); # 插入失敗，因為主表中不存在 id 為 4 的記錄

1

2

3

4

5

6

7

8

9

10

約束方式測試

insert into solider values(4, '西西里弓箭手', 3); # 成功插入 delete from country where id=3; # 會導致從表中 id 為 3 和 4 的記錄被設為 NULL update country set id=8 where id=1; # 導致從表中 country_id 為 1 的所有記錄被設為 NULL

1

2

3

以 NO ACTION（不采取行動）或 RESTRICT（禁止）方式

NO ACTION（不采取行動）或 RESTRICT（禁止），限制在從表有關聯記錄的情況下，主表不能單獨進行刪除和更新操作。保持數據的強一致性。

注：在 MySQL 中，no action 與 restrict 的功能相同。而在其他數據庫中，no action 可能為不進行任何操作。

create table solider ( id int not null, name varchar(30), country_id int, primary key(id), foreign key(country_id) references country(id) on delete RESTRICT on update RESTRICT ) ENGINE=InnoDB DEFAULT CHARSET=gb2312; # or create table solider ( id int not null, name varchar(30), country_id int, primary key(id), foreign key(country_id) references country(id) on delete no action on update no action ) ENGINE=InnoDB DEFAULT CHARSET=gb2312;

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

參照完整性測試：

# 插入主表記錄 insert into country values(1, '西歐'); insert into country values(2, '瑪雅'); insert into country values(3, '西西里'); # 插入從表記錄 insert into solider values(1, '西歐見習步兵', 1); # 插入成功 insert into solider values(2, '瑪雅短矛兵', 2); # 插入成功 insert into solider values(3, '西西里諾曼騎士', 3); # 插入成功 insert into solider values(4, '法蘭西劍士',4); # 插入失敗，因為主表中不存在 id 為 4 的記錄

1

2

3

4

5

6

7

8

9

10

約束方式測試

insert into solider values(4, '西歐騎士', 1); # 成功插入 delete from country where id=1; # 發生錯誤，從表中有關聯記錄，因此主表中不可刪除相對應記錄 update country set id=8 where id=1; # 錯誤，從表中有相關記錄，因此主表中無法修改。

1

2

3

MySQL SQL 數據庫

版權聲明：本文內容由網絡用戶投稿，版權歸原作者所有，本站不擁有其著作權，亦不承擔相應法律責任。如果您發現本站中有涉嫌抄襲或描述失實的內容，請聯系我們jiasou666@gmail.com 處理，核實后本網站將在24小時內刪除侵權內容。