跳轉到內容

結構化查詢語言/外部索引鍵

Edit links
來自華夏公益教科書，自由的教科書
資料型別 結構化查詢語言
外部索引鍵 		修改表



外部索引鍵 (FK) 定義了從一個表（子表）到另一個表（父表）的定向引用。只要兩個表中涉及的列包含相同的值，該引用就一直有效。它將子表中的一行與父表中的一行相關聯 - 父表中的一行可以與子表中的多行相關聯。

例如：您可能擁有包含列id 的表department 和包含列dept_id 的表employee 。如果您想將一名員工分配到某個特定的部門，您可以在其dept_id 列中儲存部門 ID。這在任何情況下都可以進行，無論是否定義外部索引鍵。但在這種情況下，人們通常還有兩個額外的要求：首先，員工只能分配到確實存在的部門。其次，只要員工被分配到某個特定的部門，就應該無法刪除該部門。外部索引鍵的主要目的就是保證這兩個要求。

換句話說：外部索引鍵保證不會出現孤兒。

外部索引鍵與聯結

[編輯 | 編輯原始碼]

在關係型資料庫管理系統中，相同的值用於將不同表（有時甚至是同一個表）的行連結在一起。由於這種連結是基於值而不是任何連結或特殊引用，所以它沒有方向。一般來說，我們稱這種技術為聯結。外部索引鍵具有非常類似的概念，因為它們也使用相同的值將行連結在一起。但它們也存在重要的區別。

  • 外部索引鍵具有方向。瞭解兩個受影響的表中的哪一個是子表，哪一個是父表很重要。
  • 聯結必須在所有感興趣的 DML 語句中表達（檢視除外）。相反，外部索引鍵是表定義的一部分。所有 DML 命令都會考慮到它們，而無需在 DML 語句中表達它們。

語法

[編輯 | 編輯原始碼]
-- As part of CREATE TABLE command
CREATE TABLE <table_name> (
  ...
  CONSTRAINT <constraint_name> FOREIGN KEY (<column_name>) REFERENCES <parent_table_name> (<other_column_name>)
);

-- As part of ALTER TABLE command
ALTER TABLE <table_name> ADD  CONSTRAINT <constraint_name> ... ; -- same as above
ALTER TABLE <table_name> DROP CONSTRAINT <constraint_name>;      -- throw the definition away


規則

  • FK 約束可以在表定義期間（CREATE TABLE）或之後（ALTER TABLE）定義。在本頁中，我們重點介紹 CREATE TABLE 語句。ALTER TABLE 語句的語法非常類似。
  • FK 約束屬於子表定義。
  • 儘管存在 FK 約束，但子表中的行可能不屬於任何父行。如果子行的列值為 NULL，就會發生這種情況。如果要避免這種情況，請將該列定義為“NOT NULL”。
  • 儘管 FK 約束屬於子表，但它們對父表也有影響，因此如果在子表中存在與該父行匹配的行，則無法刪除父表中的行。
  • 指定的父表必須存在。
  • 指定的父表列必須是其主鍵或唯一的列。
  • 在一個 FK 約束中使用同一個表作為父表和子表是完全可以的，參見：練習。
  • 一個表可以受許多 FK 約束的約束。

例子

[編輯 | 編輯原始碼]

該示例定義了department 和employee 表。employee 的外部索引鍵定義宣告department 是employee 的父表。

--
-- The parent table: DEPARTMENT
CREATE TABLE department (
  id         DECIMAL,
  dept_no    CHAR(10),
  dept_name  VARCHAR(100),
  CONSTRAINT dept_pk PRIMARY KEY (id)
);

-- The child table: EMPLOYEE
CREATE TABLE employee (
  id         DECIMAL,
  emp_name   VARCHAR(100),
  dept_id    DECIMAL,
  CONSTRAINT emp_pk PRIMARY KEY (id),
  CONSTRAINT emp_dept_fk FOREIGN KEY (dept_id) REFERENCES department(id)
);

-- This INSERT will fail because currently there is no department with id 10.
INSERT INTO employee   (id, emp_name, dept_id)  VALUES (1, 'Mike Baker', 10);
COMMIT;

-- It's necessary to store the department first.
INSERT INTO department (id, dept_no, dept_name) VALUES (10,'D10', 'E-Bike Development');
INSERT INTO employee   (id, emp_name, dept_id)  VALUES (1, 'Mike Baker', 10);
COMMIT;

-- The department may have a lot of employees
INSERT INTO employee   (id, emp_name, dept_id)  VALUES (2, 'Elenore McNeal', 10);
INSERT INTO employee   (id, emp_name, dept_id)  VALUES (3, 'Ted Walker', 10);
COMMIT;

-- This DELETE will fail because currently there are employees within the department.
DELETE FROM department WHERE dept_name = 'E-Bike Development';
COMMIT;

這種建模方式允許表示分層樹結構。一個或多個子節點（行）屬於一個父節點（行）。在資料庫管理系統的上下文中，這種關聯稱為 1:m 關係。

n:m 關係

[編輯 | 編輯原始碼]

在現實世界中，除了 1:m 關係之外，還有更多型別的關聯。通常，存在所謂的 n:m 關係，其中物件（行）屬於多個其他物件（行）。因此，父表/子表的含義就會消失。在我們示例資料庫 中，有一個hobby 表，另一個person 表。一個人可能擁有多個愛好。同時，多個人可能擁有相同的愛好。這可以透過在兩個原始表之間建立一個第三個表來設計。第三個表儲存第一個表和第二個表的 ID。這樣就可以決定哪個人擁有哪個愛好。

實現這種 n:m 情況的技術與上一章中 1:m 關聯中所示相同，只是它被使用了兩次。我們定義了兩個外部索引鍵，它們從“中間表”開始，並引用了另外兩個表。從技術角度來說，我們可以說“中間表”是兩個父表person 和hobby 的子表。person 和hobby 處於相同的邏輯級別。

--
CREATE TABLE t1 (
  id               DECIMAL,
  name             VARCHAR(50),
  -- ...
  CONSTRAINT t1_pk               PRIMARY KEY (id)
);
CREATE TABLE t2 (
  id               DECIMAL,
  name             VARCHAR(50),
  -- ...
  CONSTRAINT t2_pk               PRIMARY KEY (id)
);
CREATE TABLE t1_t2 (
  id               DECIMAL,
  t1_id            DECIMAL,
  t2_id            DECIMAL,
  CONSTRAINT t1_t2_pk            PRIMARY KEY (id),       -- also this table should have its own Primary Key
  CONSTRAINT t1_t2_unique        UNIQUE (t1_id, t2_id),  -- every link should occur only once
  CONSTRAINT t1_t2_fk_1          FOREIGN KEY (t1_id) REFERENCES t1(id),
  CONSTRAINT t1_t2_fk_2          FOREIGN KEY (t2_id) REFERENCES t2(id)
);

ON DELETE / ON UPDATE

[編輯 | 編輯原始碼]

到目前為止，我們假設如果在子表中存在與該父行匹配的行，則無法刪除父表中的行。這是預設行為，但總的來說，SQL 標準定義了五種選項，以各種方式處理這種父/子情況。這些選項擴充套件了約束定義。它們是

  • ON DELETE CASCADE：如果刪除父表中的一行，則刪除引用表中所有匹配的行。
  • ON DELETE SET NULL：如果刪除父表中的一行，則將子表中所有匹配行中的所有引用列設定為 NULL。
  • ON DELETE SET DEFAULT：如果刪除父表中的一行，則將子表中所有匹配行中的所有引用列設定為該列的預設值。
  • ON DELETE RESTRICT：如果在子表中存在與該行匹配的行，則禁止刪除父表中的一行。檢查發生的時刻可以延遲到 COMMIT。
  • ON DELETE NO ACTION （預設）：如果在子表中存在與該行匹配的行，則禁止刪除父表中的一行。這在所有情況下都適用，即使檢查被延遲（參見下一章）。

與 ON DELETE 選項類似，還有一個 ON UPDATE 選項。它為在父表中更改由子表列引用的列的情況定義了相同的五種選項。

  • ON UPDATE CASCADE：對父表中引用列的任何更改都會導致子表中匹配行中的對應引用列發生相同的更改。
  • ON UPDATE SET NULL：對父表中引用列的任何更改都會導致子表中匹配行中的對應引用列被設定為 null。
  • ON UPDATE SET DEFAULT：對引用表中引用列的任何更改都會導致引用表中匹配行中的對應引用列被設定為其預設值。
  • ON UPDATE RESTRICT：如果在子表中存在與該行匹配的行，則禁止更改父表中的一行。檢查發生的時刻可以延遲到 COMMIT。
  • ON UPDATE NO ACTION （預設）：如果在子表中存在與該行匹配的行，則禁止更改父表中的一行。這在所有情況下都適用，即使檢查被延遲（參見下一章）。

如果未指定 ON DELETE 或 ON UPDATE，則會執行預設操作 NO ACTION。在某些系統中，NO ACTION 是以 RESTRICT 選項的意義實現的。

一個例子

--
CREATE TABLE t1_t2 (
  ...
  CONSTRAINT t1_t2_fk_1 FOREIGN KEY (t1_id) REFERENCES t1(id)
                        ON UPDATE CASCADE ON DELETE RESTRICT,
  ...
);

提示 1：更新主鍵的概念存在爭議。

提示 2：並非所有 DBMS 都支援所有選項。

立即/延遲

[編輯 | 編輯原始碼]

還有一個額外的選項可以決定何時評估外部索引鍵定義。預設行為是在每個 UPDATE 和 DELETE 命令執行時進行檢查。第二個可能性是將檢查延遲到事務結束時，即 COMMIT 命令。延遲的目的是讓應用程式能夠在子表之前修改父表（如果它們使用 Hibernate，這可能會有所幫助）。

要定義此選項，約束定義必須透過關鍵字 [NOT] DEFERRABLE 擴充套件，這些關鍵字在 INITIALLY IMMEDIATE（預設）或 INITIALLY DEFERRED 之前或之後新增，以指定 CREATE TABLE 時刻後的初始狀態。

--
CREATE TABLE t1_t2 (
  ...
  CONSTRAINT t1_t2_fk_1 FOREIGN KEY (t1_id) REFERENCES t1(id)
                        ON UPDATE CASCADE  DEFERRABLE INITIALLY IMMEDIATE
                        ON DELETE RESTRICT DEFERRABLE INITIALLY DEFERRED,
  ...
);

提示：MySQL 不支援 DEFERRABLE 選項，但可以透過“SET foreign_key_checks = 0/1;”動態地啟用和停用外部索引鍵檢查。

雞和蛋的問題

[編輯 | 編輯原始碼]

有時應用程式會遇到迴圈依賴：表 A 包含對錶 B 的引用，反之亦然，例如：表 team 包含列 id、team_name 和 team_leader（這是一個指向球員的 ID），而表 player 包含列 id、player_name 和 team_id。

--
CREATE TABLE team (
  id               DECIMAL,
  team_name        VARCHAR(50),
  team_leader      DECIMAL,      -- ID of a player
  CONSTRAINT team_pk             PRIMARY KEY (id)
);

CREATE TABLE player (
  id               DECIMAL,
  player_name      VARCHAR(50),
  team_id          DECIMAL,
  CONSTRAINT player_pk           PRIMARY KEY (id)
);

ALTER TABLE team   ADD CONSTRAINT team_fk   FOREIGN KEY (team_leader) REFERENCES player(id);
ALTER TABLE player ADD CONSTRAINT player_fk FOREIGN KEY (team_id)     REFERENCES team(id);

到目前為止，情況都不好。當插入第一行團隊資料時，球員資料會丟失。當先插入球員資料時，團隊資料會丟失。

正如我們上面所見，存在一個 DEFER 選項。使用此選項，必須定義 FK 約束，以使它們不會立即在 INSERT 命令執行時進行評估。它們應該在所有 INSERT 操作完成後在 COMMIT 時點進行評估。

-- Throw the above definitions away ...
ALTER TABLE team   DROP CONSTRAINT team_fk;
ALTER TABLE player DROP CONSTRAINT player_fk;
-- ... and use DEFERRABLE
ALTER TABLE team   ADD  CONSTRAINT team_fk   
                        FOREIGN KEY (team_leader) REFERENCES player(id) DEFERRABLE INITIALLY DEFERRED;
ALTER TABLE player ADD  CONSTRAINT player_fk
                        FOREIGN KEY (team_id)     REFERENCES team(id)   DEFERRABLE INITIALLY DEFERRED;

現在我們可以以任何順序插入資料（不要忘記停用 AUTOCOMMIT）。

--
INSERT INTO team   (id, team_name,   team_leader) VALUES (1, 'Wild Tigers',  1);
INSERT INTO player (id, player_name, team_id)     VALUES (1, 'Johnny Crash', 1);
-- No checking of Foreign Keys up to here
COMMIT;   -- Commit includes the check of Foreign Keys

DROP TABLE / TRUNCATE TABLE

[編輯 | 編輯原始碼]

外部索引鍵對 DROP TABLE 和 TRUNCATE TABLE 命令有影響。只要外部索引鍵引用父表，就不能刪除（刪除結構和資料）或截斷（僅刪除資料）該表。即使沒有實際行引用父表中的任何行，外部索引鍵的存在就足以拒絕 DROP 和 TRUNCATE。

要使用 DROP 或 TRUNCATE，必須先刪除約束。

提示：一些實現提供了 DISABLE/ENABLE 命令，用於臨時停用約束。


練習

[編輯 | 編輯原始碼]

FK 約束的父表是否可能包含 1 行，而子表為空？

點選檢視解決方案
Yes. Parents without children are absolutely normal.

FK 約束的子表是否可能包含 1 行，而父表為空？

點選檢視解決方案
Yes. Although the main purpose of FK-constraints is the prevention of children without parents (orphans), this situation may occur.
If the column of the child row contains the NULL value, this row relates to no parent row
because 'null = <any value>' evaluates always to UNKNOWN and never to TRUE, even if that <any value> is the NULL value.

建立一個名為 genealogy 的表，用於儲存有關人員及其祖先的資訊。列為：id、first_name、last_name、birth_name、father_id、mother_id。

點選檢視解決方案
CREATE TABLE genealogy (
  id             DECIMAL                           PRIMARY KEY,
  first_name     VARCHAR(100),
  last_name      VARCHAR(100),
  birth_name     VARCHAR(100),
  father_id      DECIMAL,
  mother_id      DECIMAL
);

透過兩個 FK 約束擴充套件 genealogy 表，使列“father_id”和“mother_id”引用該表的其他行。

點選檢視解決方案
ALTER TABLE genealogy ADD CONSTRAINT gen_fk_1 FOREIGN KEY (father_id) REFERENCES genealogy(id);
ALTER TABLE genealogy ADD CONSTRAINT gen_fk_2 FOREIGN KEY (mother_id) REFERENCES genealogy(id);

將一些資料插入“genealogy”，例如：您個人家庭的資料。

點選檢視解決方案
-- For the first rows store NULL in 'father_id' and 'mother_id'!
INSERT INTO genealogy (id, first_name, last_name, birth_name, father_id, mother_id)
               VALUES (1,  'Mike',     'Miller',  'Miller',   null,      null);
INSERT INTO genealogy (id, first_name, last_name, birth_name, father_id, mother_id)
               VALUES (2,  'Eve',      'Miller',  'Summer',   null,      null);
INSERT INTO genealogy (id, first_name, last_name, birth_name, father_id, mother_id)
               VALUES (3,  'Marry',    'Dylan',   'Miller',   1,      2);
INSERT INTO genealogy (id, first_name, last_name, birth_name, father_id, mother_id)
               VALUES (4,  'Henry',    'Dylan',   'Dylan',    null,      3);
COMMIT;


資料型別 結構化查詢語言
外部索引鍵 		修改表
檢索自 "https://wikibook.tw/w/index.php?title=Structured_Query_Language/Foreign_Key&oldid=3698910"
華夏公益教科書