Oracle外键

精华

小牛编辑

113浏览

2023-03-14

在本教程中，您将学习如何使用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外键约束来强制表之间的关系。