聊聊Oracle外键约束(Foreign Key)的几个操作选项

 原文链接: 聊聊Oracle外键约束(Foreign Key)的几个操作选项

关系型数据库是以数据表和关系作为两大对象基础。数据表是以二维关系将数据组织在DBMS中,而关系建立数据表之间的关联,搭建现实对象模型。主外键是任何数据库系统都需存在的约束对象,从对象模型中的业务逻辑加以抽象,作为物理设计的一个部分在数据库中加以实现。

Oracle外键是维护参照完整性的重要手段,大多数情况下的外键都是紧密关联关系。外键约束的作用,是保证字表某个字段取值全都与另一个数据表主键字段相对应。也就是说,只要外键约束存在并有效,就不允许无参照取值出现在字表列中。具体在Oracle数据库中,外键约束还是存在一些操作选项的。本篇主要从实验入手,介绍常见操作选项。

 

1、环境介绍

 

笔者选择Oracle 11gR2进行测试,具体版本号为11.2.0.4

 

 

SQL> select * from v$version;

BANNER

--------------------------------------------------------------------------------

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

PL/SQL Release 11.2.0.4.0 - Production

CORE      11.2.0.4.0     Production

TNS for 64-bit Windows: Version 11.2.0.4.0 - Production

NLSRTL Version 11.2.0.4.0 – Production

 

 

创建数据表PrimChild,对应数据插入。

 

 

SQL> create table prim (v_id number(3), v_name varchar2(100));

Table created

 

SQL> alter table prim add constraint pk_prim primary key (v_id);

Table altered

 

SQL> create table child (c_id number(3), v_id number(3), c_name varchar2(100));

Table created

 

SQL> alter table child add constraint pk_child primary key (c_id);

Table altered

 

 

二、默认外键行为

 

首先我们查看默认外键行为方式。

 

 

SQL> alter table CHILD

  2    add constraint FK_CHILD_PRIM foreign key (V_ID)

  3    references prim (V_ID)

  4  ;

 

 

在没有额外参数加入的情况下,Oracle外键将严格按照标准外键方式工作。

 

 

--在有子记录情况下,强制删除主表记录;

SQL> delete prim where v_id=2;

delete prim where v_id=2

 

ORA-02292:违反完整约束条件(A.FK_CHILD_PRIM) - 已找到子记录

 

--在存在子表记录情况下,更改主表记录;

SQL> update prim set v_id=4 where v_id=2;

update prim set v_id=4 where v_id=2

 

ORA-02292:违反完整约束条件(A.FK_CHILD_PRIM) - 已找到子记录

 

--修改子表记录

SQL> update child set v_id=5 where v_id=2;

update child set v_id=5 where v_id=2

 

ORA-02291: 违反完整约束条件 (A.FK_CHILD_PRIM) - 未找到父项关键字

 

 

上面实验说明:在默认的Oracle外键配置条件下,只要有子表记录存在,主表记录是不允许修改或者删除的。子表记录也必须时刻保证参照完整性。

 

三、On delete cascade

 

对于应用开发人员而言,严格外键约束关系是比较麻烦的。如果直接操作数据库记录,就意味着需要手工处理主子表关系,处理删除顺序问题。On delete cascade允许了一种“先删除主表,连带删除子表记录”的功能,同时确保数据表整体参照完整性。

创建on delete cascade外键,只需要在创建外键中添加相应的子句。

 

 

SQL> alter table child add constraint FK_CHILD_PRIM foreign key(v_id) references prim(v_id) on delete cascade;

Table altered

 

 

测试:

 

 

SQL> delete prim where v_id=2;

1 row deleted

 

SQL> select * from prim;

V_ID V_NAME

---- --------------------------------------------------------------------------------

   1 kk

   3 iowkd

 

SQL> select * from child;

C_ID V_ID C_NAME

---- ---- --------------------------------------------------------------------------------

   1    1 kll

   2    1 ddkll

   3    1 43kll

 

SQL> rollback;

Rollback complete

 

 

删除主表操作成功,对应的子表记录被连带自动删除。但是其他操作依然是不允许进行。

 

 

SQL> update prim set v_id=4 where v_id=2;

update prim set v_id=4 where v_id=2

 

ORA-02292:违反完整约束条件(A.FK_CHILD_PRIM) - 已找到子记录

 

SQL> update child set v_id=5 where v_id=2;

update child set v_id=5 where v_id=2

 

ORA-02291: 违反完整约束条件 (A.FK_CHILD_PRIM) - 未找到父项关键字

 

 

On delete cascade被称为“级联删除”,对开发人员来讲是一种方便的策略,可以直接“无视”子记录而删掉主记录。但是,一般情况下,数据库设计人员和DBA一般都不推荐这样的策略。

究其原因,还是由于系统业务规则而定。On delete cascade的确在一定程度上很方便,但是这种自动操作在一些业务系统中是可能存在风险的。例如:一个系统中存在一个参数引用关系,这个参数被引用到诸如合同的主记录中。按照业务规则,如果这个参数被引用过,就不应当被删除。如果我们设置了on delete cascade外键,连带的合同记录就自动的被“干掉”了。开发参数模块的同事一般情况下,也没有足够的“觉悟”去做手工判定。基于这个因素,我们推荐采用默认的强约束关联,起码不会引起数据丢失的情况。

 

四、On Delete Set Null

 

除了直接删除记录,Oracle还提供了一种保留子表记录的策略。注意:外键约束本身不限制字段为空的问题。如果一个外键被设置为on delete set null,当删除主表记录的时候,无论是否存在子表对应记录,主表记录都会被删除,子表对应列被清空。

 

 

SQL> alter table child drop constraint fk_child_prim;

Table altered

 

SQL> alter table child add constraint FK_CHILD_PRIM foreign key(v_id) references prim(v_id) on delete set null;

Table altered

 

 

删除主表记录。

 

 

SQL> delete prim where v_id=2;

1 row deleted

 

SQL> select * from prim;

 

V_ID V_NAME

---- --------------------------------------------------------------------------------

   1 kk

   3 iowkd

 

SQL> select * from child;

 

C_ID V_ID C_NAME

---- ---- --------------------------------------------------------------------------------

   1    1 kll

   2    1 ddkll

   3    1 43kll

   4      43kll

   5      4ll

 

SQL> rollback;

Rollback complete

 

 

主表记录删除,子表外键列被清空。其他约束动作没有变化。

 

 

SQL> update prim set v_id=4 where v_id=2;

update prim set v_id=4 where v_id=2

 

ORA-02292:违反完整约束条件(A.FK_CHILD_PRIM) - 已找到子记录

 

SQL> update child set v_id=5 where v_id=2;

update child set v_id=5 where v_id=2

 

ORA-02291: 违反完整约束条件 (A.FK_CHILD_PRIM) - 未找到父项关键字

 

 

那么,下一个问题是:如果外键列不能为空,会怎么样呢?

 

 

SQL> desc child;

Name   Type          Nullable Default Comments

------ ------------- -------- ------- --------

C_ID   NUMBER(3)                              

V_ID   NUMBER(3)     Y                        

C_NAME VARCHAR2(100) Y                         

 

SQL> alter table child modify v_id not null;

Table altered

 

SQL> desc child;

Name   Type          Nullable Default Comments

------ ------------- -------- ------- --------

C_ID   NUMBER(3)                              

V_ID   NUMBER(3)                               

C_NAME VARCHAR2(100) Y                        

 

 

SQL> delete prim where v_id=2;

delete prim where v_id=2

 

ORA-01407: 无法更新 ("A"."CHILD"."V_ID") NULL

 

 

更改失败~

 

五、传说中的on update cascade

 

On update cascade被称为“级联更新”,是关系数据库理论中存在的一种外键操作类型。这种类型指的是:当主表的记录被修改(主键值修改),对应子表的外键列值连带的进行修改。

 

 

SQL> alter table child add constraint FK_CHILD_PRIM foreign key(v_id) references prim(v_id) on update cascade;

 

alter table child add constraint FK_CHILD_PRIM foreign key(v_id) references prim(v_id) on update cascade

 

ORA-00905: 缺失关键字

 

 

目前的Oracle版本中,似乎还不支持on update cascade功能。Oracle在官方服务中对这个问题的阐述是:在实际系统开发环境中,直接修改主键的情况是比较少的。所以,也许在将来的版本中,这个特性会进行支持。

 

六、结论

 

Oracle外键是我们日常比较常见的约束类型。在很多专家和业界人员的讨论中,我们经常听到“使用外键还是系统编码”的争论。支持外键策略的一般都是数据库专家和“大撒把”的设计师,借助数据库天然的特性,可以高效实现功能。支持系统编码的人员大都是“对象派”等新派人员,相信可以借助系统前端解决所有问题。

笔者对外键的观点是“适度外键,双重验证”。外键要设计在最紧密的引用关系中,对验证动作,前端和数据库端都要进行操作。外键虽然可以保证最后安全渠道,但是不能将正确易于接受的信息反馈到前端。前端开发虽然比较直观,但是的确消耗精力。所以,把握适度是重要的出发点。











 

  • 1
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
### 回答1: Oracle 中的外键约束用于维护表之间的引用完整性,它定义了一个表中的一列或多列与另一个表中的一列或多列之间的关系。这个关系可以是单向的,也可以是双向的。 在 Oracle 中,创建外键约束需要满足以下条件: 1. 外列和所引用的主列的数据类型必须相同。 2. 外列和所引用的主列的长度必须相同。 3. 外列和所引用的主列必须都已经定义为 NOT NULL。 4. 外列必须是索引列。 创建外键约束的语法如下: ``` ALTER TABLE 表名称 ADD CONSTRAINT 约束名称 FOREIGN KEY (外列名称) REFERENCES 主表名称 (主列名称); ``` 其中,表名称是指需要添加外键约束的表名,约束名称是自定义的外键约束名称,外列名称是需要添加外键约束的列名,主表名称是被引用的表名,主列名称是被引用表的主列名。 例如,创建一个名为 "orders" 的表,它有一个外列 "customer_id",引用了 "customers" 表的 "id" 列: ``` ALTER TABLE orders ADD CONSTRAINT orders_fk1 FOREIGN KEY (customer_id) REFERENCES customers (id); ``` ### 回答2: Oracle 外键约束是一种用于确保数据完整性的约束。它通过在列级别上定义关联关系,限制了一个表中的外与另一个表中的主或唯一之间的关联关系。 外键约束可以用来保证数据的一致性和完整性。当我们在一个表中创建外键约束时,它会将指定列与另一个表的主或唯一进行关联。这样,在插入、更新或删除数据时,系统会自动检查外键约束,确保数据操作不会破坏关联关系。 外键约束可以有以下几个特点: 1. 外关系必须在参照表上存在主或唯一约束。 2. 外列的数据类型必须与参照列的数据类型匹配。 3. 外列的值必须是参照表的主或唯一列中存在的值。 4. 当参照表的主或唯一更新或删除时,涉及到外操作也会受到影响。 外键约束的作用有以下几点: 1. 数据完整性:外键约束可以保证在关联表之间维护数据的一致性,防止无效或不合法数据的插入或更新。 2. 级联操作:定义级联操作可以在参照表上进行更新或删除操作时同时更新或删除关联的外表中的数据,避免了手动处理关联数据的复杂性。 3. 查询优化:外键约束可以帮助优化查询,提高数据检索性能。 4. 数据模型的清晰性:通过外键约束,可以更清晰地定义表之间的关联关系,方便他人理解数据模型。 总之,Oracle 外键约束是一种强制性的关联关系,可以保证数据的一致性和完整性,提高数据库查询性能,并且使数据模型更加清晰易懂。 ### 回答3: Oracle外键约束是一种用来保持数据完整性的约束。它定义了两个表之间的关系,并防止数据库中的无效数据。外键约束在两个表之间创建引用,一个表中的一个列是另一个表的主或唯一。 通过外键约束,我们可以确保在从表中的外列只包含在主表中已经存在的值。当我们向从表中插入或更新数据时,如果外键约束被违反,Oracle将抛出一个错误,阻止操作继续进行。 外键约束可以执行以下操作: 1. 防止删除或更新主表中的行,如果在从表中的外列中仍然存在引用。这可以防止出现孤儿记录。 2. 防止插入或更新从表中的行,如果在主表中没有对应的主或唯一值。这可以防止出现无效的引用。 通过使用外键约束,我们可以确保数据库中的数据完整性和一致性。它可以减少数据错误和无效引用的可能性,并确保数据库中的相关数据保持一致。 外键约束可以在创建表时定义,也可以在后期通过ALTER TABLE语句添加。我们可以选择指定外键约束的名称,以及在违反约束时的动作,如RESTRICT、CASCADE或SET NULL。 总而言之,Oracle外键约束可以确保在表之间保持有效关系,防止无效引用和孤儿记录,并维护数据库的数据完整性和一致性。

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值