Oracle外键约束（Foreign Key）的几个操作选项

张兴康

关系型数据库是以数据表和关系作为两大对象基础。数据表是以二维关系将数据组织在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外键是我们日常比较常见的约束类型。在很多专家和业界人员的讨论中，我们经常听到“使用外键还是系统编码”的争论。支持外键策略的一般都是数据库专家和“大撒把”的设计师，借助数据库天然的特性，可以高效实现功能。

张兴康

fteair

fteair

fteair

fteair

张兴康