Oracle外键约束三种删除行为 _MySQL, Oracle及数据库讨论区_Weblogic技术|Tuxedo技术|中间件技术|Oracle论坛|JAVA论坛|Linux/Unix技术|hadoop论坛

总帖数

每页帖数

1/1页

返回列表

发起投票

查看: 2439 | 回复: 0

主题： Oracle外键约束三种删除行为

wayne

注册用户

等级：中校
经验：1690
发帖：221
精华：0
注册：2011-7-21
状态：离线
发送短消息息给wayne

加好友发送短消息息给wayne

发消息

发表于：

2015-6-18 15:22:02 | [全部帖] [楼主帖]

楼主

Oracle使用外键来限制子表中参考的字段值，要求子表中的数据必须在主表中存在。当主表的记录发生变化时导致外键参考唯一约束值发生了变化时，Oracle指定了三种动作：默认值（类似于restrict）、delete cascade和delete set null。

1.创建主表及子表并简单初始化几条数据
1）创建主表t_parent，并初始化三条记录

sec@ora10g> create table t_parent (parent_id int primary key, name varchar2(10));
sec@ora10g> insert into t_parent values (1,'record1');
sec@ora10g> insert into t_parent values (2,'record2');
sec@ora10g> insert into t_parent values (3,'record3');
sec@ora10g> commit;

2）创建三种类型的子表t_child1、t_child2和t_child3
（1）no action类型

sec@ora10g> create table t_child1 (child1_id int primary key, parent_id int);
sec@ora10g> alter table t_child1 add constraint FK_t_child1 foreign key (parent_id) references t_parent (parent_id);
sec@ora10g> insert into t_child1 values (1,1);
sec@ora10g> commit;

（1）cascade类型

sec@ora10g> create table t_child2 (child2_id int primary key, parent_id int);
sec@ora10g> alter table t_child2 add constraint FK_t_child2 foreign key (parent_id) references t_parent (parent_id) on delete cascade;
sec@ora10g> insert into t_child2 values (2,2);
sec@ora10g> commit;

（1）set null类型

sec@ora10g> create table t_child3 (child2_id int primary key, parent_id int);
sec@ora10g> alter table t_child3 add constraint FK_t_child3 foreign key (parent_id) references t_parent (parent_id) on delete set null;
sec@ora10g> insert into t_child3 values (3,3);
sec@ora10g> commit;

2.确认主表和子表中的数据

sec@ora10g> select * from T_PARENT;
PARENT_ID NAME
---------- ------------------------------
1 record1
2 record2
3 record3
sec@ora10g> select * from T_CHILD1;
CHILD1_ID  PARENT_ID
---------- ----------
1          1
sec@ora10g> select * from T_CHILD2;
CHILD2_ID  PARENT_ID
---------- ----------
2          2
sec@ora10g> select * from T_CHILD3;
CHILD2_ID  PARENT_ID
---------- ----------
3          3

3.尝试对具有默认类型外键参照的主表记录进行删除

sec@ora10g> delete from T_PARENT where parent_id = 1;
delete from T_PARENT where parent_id = 1
*
ERROR at line 1:
ORA-02292: integrity constraint (SEC.FK_T_CHILD1) violated - child record found
sec@ora10g> select * from T_CHILD1;
CHILD1_ID PARENT_ID
---------- ----------
1 1

在此类型下，不允许删除。

4.尝试对具有delete cascade类型外键参照的主表记录进行删除

sec@ora10g> delete from T_PARENT where parent_id = 2;
1 row deleted.
sec@ora10g> select * from T_CHILD2;
no rows selected

成功，级联删除成功。

5.尝试对具有delete set null类型外键参照的主表记录进行删除

sec@ora10g> delete from T_PARENT where parent_id = 3;
1 row deleted.
sec@ora10g> select * from T_CHILD3;
CHILD2_ID PARENT_ID
---------- ----------
3

主表记录可以完成删除，子表中对应的内容被设置为NULL。

--转自