SQL 外部索引鍵約束基本指南

摘要：在本教程中，您將學習 SQL 外部索引鍵以及如何使用 FOREIGN KEY 約束來定義外部索引鍵。

SQL 外部索引鍵簡介 #

在 SQL 中，外部索引鍵是一個表中的一個或一組列，它引用另一個表的主鍵。

包含外部索引鍵的表稱為子表或外部索引鍵表，或引用表。
其主鍵列被外部索引鍵引用的表稱為父表或被引用表。

外部索引鍵的主要目的是建立子表和父表之間的關係。

例如，對於子表中外部索引鍵的某個值，您總能在父表的主鍵中找到相應的值。

假設我們有兩個表：projects 和 project_milestones。

project_milestones 表有一個外部索引鍵 project_id，它引用 projects 表中的 project_id。
project_milestones 表稱為子表或外部索引鍵表，而 projects 表是父表或被引用表。

對於 project_milestones 表中的每一行，您都可以在 projects 表中找到 project_id 的值。這個規則被稱為兩個表之間的引用完整性約束。

一個表可以有多個外部索引鍵，但只能有一個主鍵。

在 SQL 中，您使用外部索引鍵約束來建立外部索引鍵。

SQL 外部索引鍵約束 #

以下是在子表中定義外部索引鍵約束的語法：

CONSTRAINT constraint_name
FOREIGN KEY (column1, column2)
REFERENCES parent_table(column1, column2)
ON DELETE delete_action
ON UPDATE update_action;Code language: SQL (Structured Query Language) (sql)

在此語法中：

首先，在 CONSTRAINT 子句中指定外部索引鍵的約束名稱。如果您不為外部索引鍵命名，大多數資料庫系統會自動分配一個名稱。
其次，提供一個或多個要包含在外部索引鍵中的列。
第三，指定父表（被引用表）及其一個或多個列。
最後，定義當父表主鍵中的值被更新或刪除時的操作。

定義外部索引鍵約束示例 #

第 1 步：建立一個新表，名為 projects，用於儲存專案資料。

CREATE TABLE projects (
    project_id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    project_name VARCHAR(255) NOT NULL,
    start_date DATE NOT NULL,
    end_date DATE NOT NULL
);Code language: SQL (Structured Query Language) (sql)

試一試

第 2 步：建立一個新表，名為 project_milestones，用於儲存專案里程碑。

CREATE TABLE project_milestones (
  milestone_id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  milestone VARCHAR(255),
  start_date DATE NOT NULL,
  end_date DATE NOT NULL,
  project_id INT NOT NULL,
  FOREIGN KEY (project_id) REFERENCES projects (project_id)
);Code language: SQL (Structured Query Language) (sql)

試一試

在 project_milestones 表中，我們定義了一個包含 project_id 的外部索引鍵約束，並將其引用到 projects 表的 project_id 列。

由於我們沒有明確指定外部索引鍵約束的名稱，資料庫系統會自動生成一個名為 project_milestones_project_id_fkey 的名稱。

請注意，生成的名稱可能因資料庫系統而異。

使用外部索引鍵約束插入資料 #

第 1 步：向 projects 表中插入一個新行。

INSERT INTO
  projects (project_name, start_date, end_date)
VALUES
  ('Super App', '2025-01-01', '2025-12-31');Code language: SQL (Structured Query Language) (sql)

試一試

第 2 步：嘗試向 project_milestones 表中插入一個新行，其 project_id 值在 projects 表中不存在。

INSERT INTO
  project_milestones (milestone, start_date, end_date, project_id)
VALUES
  ('Initiation', '2025-01-01', '2025-01-31', 0);Code language: SQL (Structured Query Language) (sql)

試一試

資料庫系統將發出類似這樣的錯誤訊息：

ERROR:  insert or update on table "project_milestones" violates foreign key constraint "project_milestones_project_id_fkey"
DETAIL:  Key (project_id)=(0) is not present in table "projects".Code language: SQL (Structured Query Language) (sql)

該錯誤訊息指出 project_id 為 0 的值在 projects 表中不存在。

第 3 步：向 project_milestones 表中插入一個具有有效 project_id 值的新行。

INSERT INTO
  project_milestones (milestone, start_date, end_date, project_id)
VALUES
  ('Initiation', '2025-01-01', '2025-01-31', 1);Code language: SQL (Structured Query Language) (sql)

試一試

向現有表新增 FOREIGN KEY 約束 #

要向表中新增外部索引鍵約束，您可以使用 ALTER TABLE ... ADD CONSTRAINT 語句。

ALTER TABLE foreign_key_table
ADD CONSTRAINT constraint_name
FOREIGN KEY (column1, column2)
     REFERENCES parent_table(column1, column2)
     ON UPDATE update_action
     ON DELETE delete_action;Code language: SQL (Structured Query Language) (sql)

如果表中已有資料，在向外鍵表新增外部索引鍵約束之前，您必須確保資料是有效的。否則，您會遇到約束衝突錯誤。

第 1 步：建立一個名為 project_tasks 的表。

CREATE TABLE project_tasks (
  task_id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  title VARCHAR(255) NOT NULL,
  completed BOOL NOT NULL DEFAULT FALSE,
  start_date DATE NOT NULL,
  end_date DATE NOT NULL,
  milestone_id INT
);Code language: SQL (Structured Query Language) (sql)

試一試

第 2 步：向 project_tasks 表新增一個外部索引鍵約束。

ALTER TABLE project_tasks
ADD CONSTRAINT project_tasks_milestone_id_fkey 
FOREIGN KEY (milestone_id) 
REFERENCES project_milestones (milestone_id);Code language: SQL (Structured Query Language) (sql)

試一試

刪除外部索引鍵約束 #

要刪除外部索引鍵約束，您可以使用 ALTER TABLE 語句。

ALTER TABLE foreign_key_table
DROP CONSTRAINT fk_name;Code language: SQL (Structured Query Language) (sql)

例如，以下語句移除了 project_tasks_milestone_id_fkey 外部索引鍵約束。

ALTER TABLE project_tasks
DROP CONSTRAINT project_tasks_milestone_id_fkey;Code language: SQL (Structured Query Language) (sql)

試一試

刪除操作 #

當您刪除父表中的一行時，資料庫系統需要決定如何處理子表中的行。

例如，如果您從 projects 表中刪除一行，資料庫系統必須知道如何處理那些 project_id 與被刪除專案相關的行。

預設情況下，如果子表中存在相關行，資料庫系統不允許您刪除父表中的行。如果您嘗試這樣做，資料庫系統將拒絕刪除併發出錯誤。

SQL 外部索引鍵的刪除操作

RESTRICT：當子表中有行引用父表中的值時，您不能刪除父表中的該行。
SET NULL：當您從父表中刪除相關行時，資料庫系統會將外部索引鍵列中的值設定為 NULL。
SET DEFAULT：當您從父表中刪除相關行時，資料庫系統會用其預設值更新外部索引鍵列。
CASCADE：當您從父表中刪除相關行時，資料庫系統會刪除子表中的相關行。

更新操作 #

更新操作指示資料庫系統在您更新父表中的相關行時如何處理子表中的行。

SQL 為外部索引鍵定義了以下刪除操作：

RESTRICT：如果子表中存在依賴行，資料庫系統會阻止更新主鍵。這是預設行為。
SET NULL：當您更新父表的主鍵值時，資料庫系統會將相關行的外部索引鍵列值設定為 NULL。
SET DEFAULT：當您更新父表的主鍵值時，資料庫系統會將相關行的外部索引鍵列值設定為其預設值。
CASCADE：當您更新父表的主鍵值時，資料庫系統會自動更新外部索引鍵列中的值。

在實踐中，您很少更新主鍵值。因此，您很少需要 ON UPDATE 操作。

摘要 #

使用 SQL 外部索引鍵約束來強制執行兩個表之間的引用完整性。

測驗 #

資料庫 #

本教程是否有幫助？