Oracle教學 » Oracle外來鍵

Oracle外來鍵

在本教學中，您將學習如何使用Oracle外來鍵來建立表與表之間的關係。

Oracle外來鍵約束簡介

外來鍵就是表與表的關係，比如：一個表的一例參照另外一個表的一列。 我們從一個簡單例子開始，清楚地理解它的概念。

假設，有兩個表：supplier_groups和supplier 分別用來儲存供應商分組和供應商資訊，如下建立語句：

CREATE TABLE supplier_groups(
    group_id NUMBER GENERATED BY DEFAULT AS IDENTITY,
    group_name VARCHAR2(255) NOT NULL,
    PRIMARY KEY (group_id)  
);

CREATE TABLE suppliers (
    supplier_id NUMBER GENERATED BY DEFAULT AS IDENTITY,
    supplier_name VARCHAR2(255) NOT NULL,
    group_id NUMBER NOT NULL,
    PRIMARY KEY(supplier_id)
);

supplier_groups表儲存供應商組，例如一次性供應商，第三方供應商和跨公司供應商。 每個供應商組可能有零個，一個或多個供應商。

suppliers表儲存供應商資訊。每個供應商必須屬於一個供應商組織。

supplier_groups和supplier表之間的關係是一對多關係。換句話說，一個供應商組有許多供應商，而每個供應商必須屬於一個供應商組。

suppliers表中的group_id用於建立suppliers和supplier_groups表中的行之間的關係。

在suppliers表中插入一行之前，必須在supplier_groups表中查詢現有的group_id，並使用該值進行插入。

假設supplier_groups表包含以下資料：

INSERT INTO supplier_groups(group_name) 
VALUES('One-time Supplier');

INSERT INTO supplier_groups(group_name) 
VALUES('Third-party Supplier');

INSERT INTO supplier_groups(group_name)
VALUES('Inter-co Supplier');

SELECT
    *
FROM
    supplier_groups;

執行上面查詢語句，得到以下結果 -

要插入新的第三方供應商，必須指定group_id的值為：2，如下所示：

INSERT INTO suppliers(supplier_name, group_id)
VALUES('Toshiba', 2);

它按預期那樣工作。不過，下面的說法也適用：

INSERT INTO suppliers(supplier_name, group_id)
VALUES('WD',4);

supplier_groups表沒有分組ID為4的行，但沒有阻止您將其插入到suppliers表中，這是一個問題。

例如，以下查詢無法獲得所有供應商及其分組：

SELECT
    supplier_name,
    group_name
FROM
    suppliers
INNER JOIN supplier_groups
        USING(group_id);

執行上面查詢程式碼，得到以下結果 -

如您所見，WD供應商在結果集中缺失。

解決此問題的一個解決方案是使用Oracle外來鍵約束來強制supplier_groups和suppliers表中的行之間建立外來鍵關係。

首先，刪除suppliers表：

DROP TABLE suppliers;

其次，用外來鍵約束重新建立suppliers表：

CREATE TABLE suppliers (
    supplier_id NUMBER GENERATED BY DEFAULT AS IDENTITY,
    supplier_name VARCHAR2(255) NOT NULL,
    group_id NUMBER NOT NULL,
    PRIMARY KEY(supplier_id),
    FOREIGN KEY(group_id) REFERENCES supplier_groups(group_id)
);

在這個語句中，新增了以下子句：

FOREIGN KEY(group_id) REFERENCES supplier_groups(group_id)

該子句指示suppliers表中的group_id列定義為參照了supplier_groups表的group_id列做為外來鍵。

這樣，這個約束就被Oracle強制執行了。 換句話說，試圖在suppliers表中插入一行不與supplier_groups表中的任何行相對應的行時將失敗，如果試圖從supplier_groups表中刪除suppliers表中存在相關行時，也會出現錯誤。

suppliers表稱為子表，而supplier_groups稱為父表。 為了擴充套件父子分類層次關係，從父表(supplier_groups)獲取主鍵值並將其插入到子表(suppliers)中，即子表使用FOREIGN KEY時，它繼承父表的外來鍵列(group_id)。

順便說一下，參照完整性的概念就是保持和執行這種父子關係。

Oracle操作中的外來鍵約束

以下語句有效，因為supplier_groups表有group_id列的值是：1 的一行：

INSERT INTO suppliers(supplier_name, group_id)
VALUES('Toshiba',1);

但是，執行以下語句將失敗：

INSERT INTO suppliers(supplier_name, group_id)
VALUES('WD',4);

因為supplier_groups沒有id為4的行。所以會發出以下是錯誤訊息：

SQL Error: ORA-02291: integrity constraint (OT.SYS_C0010646) violated - parent key not found

同樣，試圖刪除supplier_groups表中group_id列值為1的行將失敗：

DELETE
FROM
    supplier_groups
WHERE
    group_id = 1;

Oracle發布了以下錯誤訊息：

SQL Error: ORA-02292: integrity constraint (OT.SYS_C0010654) violated - child record found

由於suppliers表(子表)有一個參照行被刪除的行。

Oracle允許建立，新增，刪除，禁用和啟用外來鍵約束。

建立一個外來鍵約束

以下語句說明建立表時建立外來鍵約束的語法：

CREATE TABLE child_table (
    ...
    CONSTRAINT fk_name
    FOREIGN KEY(col1, col2,...) REFERENCES parent_table(col1,col2) 
    ON DELETE [ CASCADE | SET NULL ]
);

下面來仔細看看一下這個語句。

首先，要顯式地為外來鍵約束指定一個名稱，可以使用CONSTRAINT子句，後跟名稱。 CONSTRAINT子句是可選的。如果忽略它，Oracle會為外來鍵約束分配一個系統生成的名字。
其次，指定FOREIGN KEY子句，將一個或多個列定義為具有外來鍵列參照的列的外來鍵和父表。
第三，當刪除父表中的行時，使用ON DELETE子句來指定結果。

  • ON DELETE CASCADE：如果父項中的一行被刪除，那麼子表中所有參照該行的行都將被刪除。
  • ON DELETE SET NULL：如果父項中的一行被刪除，那麼對該外來鍵列的參照該行的子表中的所有行將被設定為NULL。

與主鍵約束不同，表可能有多個外來鍵約束。

將外來鍵約束新增到表中

如果要將外來鍵約束新增到現有表中，請按如下所示使用ALTER TABLE語句：

ALTER TABLE child_table 
ADD CONSTRAINT fk_name
FOREIGN KEY (col1,col2) REFERENCES child_table (col1,col2);

刪除外來鍵約束

要刪除外來鍵約束，請使用下面的ALTER TABLE語句：

ALTER TABLE child_table
DROP CONSTRAINT fk_name;

禁用外來鍵約束

要暫時禁用外部約束，請使用以下ALTER TABLE語句：

ALTER TABLE child_table
DISABLE CONSTRAINT fk_name;

啟用外部約束

同樣，也可以使用ALTER TABLE語句啟用禁用的外來鍵約束：

ALTER TABLE child_table
ENABLE CONSTRAINT fk_name;

在本教學中，您已學習如何使用Oracle外來鍵約束來強制表之間的關係。