数据库教程-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; 

执行上面查询语句,得到以下结果 –
Oracle外键

要插入新的第三方供应商,必须指定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); 

执行上面查询代码,得到以下结果 –

Oracle外键

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

  

从零到一,创造未来!跨零综合IT问题解决服务站,欢迎你的到来。Oracle数据库教程 只为你绽放。

本文固定链接: http://kua0.com/2019/01/16/数据库教程-oracle外键/

为您推荐

发表评论

电子邮件地址不会被公开。 必填项已用*标注