Oracle外键

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外键约束来强制表之间的关系。