度量快速开发平台-业界最好的全功能软件开发平台

 找回密码
 立即注册

QQ登录

只需一步,快速开始

搜索
热搜: 部件 流程 SQL
查看: 86|回复: 6

[分享] Oracle外键约束(Foreign Key)的几个操作选项

[复制链接]

542

主题

5919

帖子

1万

积分

作者

Rank: 7Rank: 7Rank: 7

积分
13589
发表于 2018-2-28 17:19:43 | 显示全部楼层 |阅读模式
关系型数据库是以数据表和关系作为两大对象基础。数据表是以二维关系将数据组织在DBMS中,而关系建立数据表之间的关联,搭建现实对象模型。主外键是任何数据库系统都需存在的约束对象,从对象模型中的业务逻辑加以抽象,作为物理设计的一个部分在数据库中加以实现。

        Oracle外键是维护参照完整性的重要手段,大多数情况下的外键都是紧密关联关系。外键约束的作用,是保证字表某个字段取值全都与另一个数据表主键字段相对应。也就是说,只要外键约束存在并有效,就不允许无参照取值出现在字表列中。具体在Oracle数据库中,外键约束还是存在一些操作选项的。
1、环境介绍:
  1. SQL> select * from v$version;
  2. BANNER
  3. --------------------------------------------------------------------------------
  4. Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
  5. PL/SQL Release <font color="red">11.2.0.4.0</font> - Production
  6. CORE      11.2.0.4.0     Production
  7. TNS for 64-bit Windows: Version 11.2.0.4.0 - Production
  8. NLSRTL Version 11.2.0.4.0 – Production
复制代码

创建数据表Prim和Child,对应数据插入
  1. SQL> create table prim (v_id number(3), v_name varchar2(100));
  2. Table created
  3.   
  4. SQL> alter table prim add constraint pk_prim primary key (v_id);
  5. Table altered
  6.   
  7. SQL> create table child (c_id number(3), v_id number(3), c_name varchar2(100));
  8. Table created
  9.   
  10. SQL> alter table child add constraint pk_child primary key (c_id);
  11. Table altered
复制代码
二、默认外键行为
        首先我们查看默认外键行为方式。
  1. SQL> alter table CHILD
  2.   2    add constraint FK_CHILD_PRIM foreign key (V_ID)
  3.   3    references prim (V_ID)
  4.   4  ;
复制代码
在没有额外参数加入的情况下,Oracle外键将严格按照标准外键方式工作。
  1. --在有子记录情况下,强制删除主表记录;
  2. SQL> delete prim where v_id=2;
  3. delete prim where v_id=2
  4.   
  5. ORA-02292:违反完整约束条件(A.FK_CHILD_PRIM) - 已找到子记录
  6.   
  7. --在存在子表记录情况下,更改主表记录;
  8. SQL> update prim set v_id=4 where v_id=2;
  9. update prim set v_id=4 where v_id=2
  10.   
  11. ORA-02292:违反完整约束条件(A.FK_CHILD_PRIM) - 已找到子记录
  12.   
  13. --修改子表记录
  14. SQL> update child set v_id=5 where v_id=2;
  15. update child set v_id=5 where v_id=2
  16.   
  17. ORA-02291: 违反完整约束条件 (A.FK_CHILD_PRIM) - 未找到父项关键字
复制代码
上面实验说明:在默认的Oracle外键配置条件下,只要有子表记录存在,主表记录是不允许修改或者删除的。子表记录也必须时刻保证参照完整性。
        三、On delete cascade
        对于应用开发人员而言,严格外键约束关系是比较麻烦的。如果直接操作数据库记录,就意味着需要手工处理主子表关系,处理删除顺序问题。On delete cascade允许了一种“先删除主表,连带删除子表记录”的功能,同时确保数据表整体参照完整性。
        创建on delete cascade外键,只需要在创建外键中添加相应的子句。
  1. SQL> alter table child add constraint FK_CHILD_PRIM foreign key(v_id) references prim(v_id) on delete cascade;
  2. Table altered
复制代码
测试:
  1. SQL> delete prim where v_id=2;
  2. 1 row deleted
  3.   
  4. SQL> select * from prim;
  5. V_ID V_NAME
  6. ---- --------------------------------------------------------------------------------
  7.    1 kk
  8.    3 iowkd
  9.   
  10. SQL> select * from child;
  11. C_ID V_ID C_NAME
  12. ---- ---- --------------------------------------------------------------------------------
  13.    1    1 kll
  14.    2    1 ddkll
  15.    3    1 43kll
  16.   
  17. SQL> rollback;
  18. Rollback complete
复制代码
删除主表操作成功,对应的子表记录被连带自动删除。但是其他操作依然是不允许进行。
  1. SQL> update prim set v_id=4 where v_id=2;
  2. update prim set v_id=4 where v_id=2
  3.   
  4. ORA-02292:违反完整约束条件(A.FK_CHILD_PRIM) - 已找到子记录
  5.   
  6. SQL> update child set v_id=5 where v_id=2;
  7. update child set v_id=5 where v_id=2
  8.   
  9. 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,当删除主表记录的时候,无论是否存在子表对应记录,主表记录都会被删除,子表对应列被清空。
  1. SQL> alter table child drop constraint fk_child_prim;
  2. Table altered
  3.   
  4. SQL> alter table child add constraint FK_CHILD_PRIM foreign key(v_id) references prim(v_id) on delete set null;
  5. Table altered
复制代码
删除主表记录。
  1. SQL> delete prim where v_id=2;
  2. 1 row deleted
  3.   
  4. SQL> select * from prim;
  5.   
  6. V_ID V_NAME
  7. ---- --------------------------------------------------------------------------------
  8.    1 kk
  9.    3 iowkd
  10.   
  11. SQL> select * from child;
  12.   
  13. C_ID V_ID C_NAME
  14. ---- ---- --------------------------------------------------------------------------------
  15.    1    1 kll
  16.    2    1 ddkll
  17.    3    1 43kll
  18.    4      43kll
  19.    5      4ll
  20.   
  21. SQL> rollback;
  22. Rollback complete
复制代码
主表记录删除,子表外键列被清空。其他约束动作没有变化。
  1. SQL> update prim set v_id=4 where v_id=2;
  2. update prim set v_id=4 where v_id=2
  3.   
  4. ORA-02292:违反完整约束条件(A.FK_CHILD_PRIM) - 已找到子记录
  5.   
  6. SQL> update child set v_id=5 where v_id=2;
  7. update child set v_id=5 where v_id=2
  8.   
  9. ORA-02291: 违反完整约束条件 (A.FK_CHILD_PRIM) - 未找到父项关键字
  10.   
复制代码
那么,下一个问题是:如果外键列不能为空,会怎么样呢?
  1. SQL> desc child;
  2. Name   Type          Nullable Default Comments
  3. ------ ------------- -------- ------- --------
  4. C_ID   NUMBER(3)                              
  5. V_ID   NUMBER(3)     Y                        
  6. C_NAME VARCHAR2(100) Y                          
  7.   
  8. SQL> alter table child modify v_id not null;
  9. Table altered
  10.   
  11. SQL> desc child;
  12. Name   Type          Nullable Default Comments
  13. ------ ------------- -------- ------- --------
  14. C_ID   NUMBER(3)                              
  15. V_ID   NUMBER(3)                                
  16. C_NAME VARCHAR2(100) Y                        
  17.   
  18.   
  19. SQL> delete prim where v_id=2;
  20. delete prim where v_id=2
  21.   
  22. ORA-01407: 无法更新 ("A"."CHILD"."V_ID")为 NULL
复制代码
更改失败~
五、on update cascade
On update cascade被称为“级联更新”,是关系数据库理论中存在的一种外键操作类型。这种类型指的是:当主表的记录被修改(主键值修改),对应子表的外键列值连带的进行修改。
  1. SQL> alter table child add constraint FK_CHILD_PRIM foreign key(v_id) references prim(v_id) on update cascade;
  2.   
  3. alter table child add constraint FK_CHILD_PRIM foreign key(v_id) references prim(v_id) on update cascade
  4.   
  5. ORA-00905: 缺失关键字
复制代码
六、结论
        Oracle外键是我们日常比较常见的约束类型。在很多专家和业界人员的讨论中,我们经常听到“使用外键还是系统编码”的争论。支持外键策略的一般都是数据库专家和“大撒把”的设计师,借助数据库天然的特性,可以高效实现功能。

回复

使用道具 举报

542

主题

5919

帖子

1万

积分

作者

Rank: 7Rank: 7Rank: 7

积分
13589
 楼主| 发表于 2018-2-28 17:20:09 | 显示全部楼层
回复 支持 反对

使用道具 举报

231

主题

2541

帖子

5807

积分

论坛元老

Rank: 8Rank: 8

积分
5807
发表于 2018-2-28 17:39:38 | 显示全部楼层
回复 支持 反对

使用道具 举报

231

主题

2541

帖子

5807

积分

论坛元老

Rank: 8Rank: 8

积分
5807
发表于 2018-3-1 17:41:50 | 显示全部楼层
回复 支持 反对

使用道具 举报

231

主题

2541

帖子

5807

积分

论坛元老

Rank: 8Rank: 8

积分
5807
发表于 2018-3-1 17:42:14 | 显示全部楼层
回复 支持 反对

使用道具 举报

231

主题

2541

帖子

5807

积分

论坛元老

Rank: 8Rank: 8

积分
5807
发表于 2018-3-5 17:48:07 | 显示全部楼层
回复 支持 反对

使用道具 举报

542

主题

5919

帖子

1万

积分

作者

Rank: 7Rank: 7Rank: 7

积分
13589
 楼主| 发表于 2018-3-7 14:01:17 | 显示全部楼层
回复 支持 反对

使用道具 举报

您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

手机版|小黑屋|重庆度量科技  本站关键词:快速开发平台

GMT+8, 2018-6-25 10:23 , Processed in 0.359215 second(s), 25 queries .

Powered by Discuz! X3.2

© 2001-2013 Comsenz Inc.

快速回复 返回顶部 返回列表