Oracle外來鍵


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

Oracle外來鍵約束簡介

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

假設,有兩個表:supplier_groupssupplier 分別用來儲存供應商分組和供應商資訊,如下建立語句:

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_groupssupplier表之間的關係是一對多關係。換句話說,一個供應商組有許多供應商,而每個供應商必須屬於一個供應商組。

suppliers表中的group_id用於建立supplierssupplier_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表沒有分組ID4的行,但沒有阻止您將其插入到suppliers表中,這是一個問題。

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

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

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

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

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

首先,刪除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沒有id4的行。所以會發出以下是錯誤訊息:

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外來鍵約束來強制表之間的關係。