ITPub博客

首页 > Linux操作系统 > Linux操作系统 > Oracle 外键的级联处理

Oracle 外键的级联处理

原创 Linux操作系统 作者:regonly1 时间:2009-08-05 20:59:44 0 删除 编辑
昨天在Oracle042模拟题中看到一个关于外键级联删除的题目,由于对这个概念不熟,
所以做错了。今天做个实验看看。
对一个表做另一个表的外键约束时,可以指定级联数据删除。
也就是当父表删除一条记录时,子表对应父表外键关联的记录也会被删除。
首先创建演示测试表:
[oracle@localhost 20090805]$ cat 1.sql
create table pf(id number(10) primary key);
insert into pf(id) values(1);
insert into pf(id) values(2);
insert into pf(id) values(3);
insert into pf(id) values(4);
commit;
create table pc(id number(10) primary key, parent_id number(10),
                                constraints fk_parentid foreign key(parent_id)
                                references pf(id) on delete cascade);
insert into pc(id, parent_id) values(1,1);
insert into pc(id, parent_id) values(2,3);
insert into pc(id, parent_id) values(3,5);
insert into pc(id, parent_id) values(4,3);
insert into pc(id, parent_id) values(5,2);
insert into pc(id, parent_id) values(6,1);
commit;
[oracle@localhost 20090805]$cat 1.sql | sqlplus lyon/passwd
 
创建表和数据完成。
从下面的例子也可以看出,当delete父表的id=1的数据时查看被锁定的TM锁有两个
(TM锁即为表级锁),分别为pf和pc这两个表。
当提交后pf中id=1且子表中parent_id=1的数据都被同步删除了。对应的表级锁也同
时解除:
20:59:37 lyon@ORCL> select * from pf;
        ID
----------
         1
         2
         3
         4
20:59:41 lyon@ORCL> select * from pc;
        ID  PARENT_ID
---------- ----------
         1          1
         2          3
         4          3
         5          2
         6          1
20:59:44 lyon@ORCL>  select object_name, l.* from v$lock l, dba_objects o where l.id1 = o.data_object_id and l.type in('TX','TM');
no rows selected
20:59:51 lyon@ORCL> delete from pf where id = 1;
1 row deleted.
20:59:58 lyon@ORCL>  select object_name, l.* from v$lock l, dba_objects o where l.id1 = o.data_object_id and l.type in('TX','TM');
OBJECT_NAM ADDR     KADDR           SID TYPE          ID1        ID2      LMODE    REQUEST      CTIME      BLOCK
---------- -------- -------- ---------- ------ ---------- ---------- ---------- ---------- ---------- ----------
PF         3F9C3F90 3F9C3FA8        151 TM          53423          0          3          0          0          0
PC         3F9C403C 3F9C4054        151 TM          53425          0          3          0          0          0
20:59:59 lyon@ORCL> commit;
Commit complete.
21:00:06 lyon@ORCL>  select object_name, l.* from v$lock l, dba_objects o where l.id1 = o.data_object_id and l.type in('TX','TM');
no rows selected
21:00:07 lyon@ORCL> select * from pf;
        ID
----------
         2
         3
         4
21:00:12 lyon@ORCL> select * from pc;
        ID  PARENT_ID
---------- ----------
         2          3
         4          3
         5          2
21:00:15 lyon@ORCL>
这就引出了一个问题,默认的情况是否是级联删除的呢?做个实验:
21:16:43 lyon@ORCL> !cat 2.sql
drop table pc2;
create table pc2(id number(10),
                                 parent_id number(10),
                                 constraint fk_parent_id2
                                 foreign key(parent_id)
                                 references pf(id));
insert into pf(id) values(1);
insert into pc2(id, parent_id) values(1,1);
insert into pc2(id, parent_id) values(2,3);
insert into pc2(id, parent_id) values(3,5);
insert into pc2(id, parent_id) values(4,3);
insert into pc2(id, parent_id) values(5,2);
insert into pc2(id, parent_id) values(6,1);
commit;
21:16:16 lyon@ORCL> delete from pf where pf.id = 1;
delete from pf where pf.id = 1
*
ERROR at line 1:
ORA-02292: integrity constraint (LYON.FK_PARENT_ID2) violated - child record found
 
此时删除pf.id = 1时,出现了如上的错误。
先删除pc2.parent_id=1看看:
20:32:56 lyon@ORCL> delete from pc2 where parent_id = 1;
2 rows deleted.
21:19:00 lyon@ORCL> col object_name format A10;
21:19:28 lyon@ORCL> select object_name, l.* from v$lock l, dba_objects o where o.data_object_id = l.id1 and l.type in('TX','TM');
OBJECT_NAM ADDR     KADDR           SID TYPE          ID1        ID2      LMODE    REQUEST      CTIME      BLOCK
---------- -------- -------- ---------- ------ ---------- ---------- ---------- ---------- ---------- ----------
PF         3F9C3F90 3F9C3FA8        146 TM          53423          0          2          0         63          0
PC2        3F9C403C 3F9C4054        146 TM          53428          0          3          0         63          0
21:20:20 lyon@ORCL> select object_name, l.* from v$lock l, dba_objects o where o.data_object_id = l.id1 and l.type in('TX','TM');
no rows selected
可以看到pc2删除正常,但是有个疑问是,删除pc2的时候也会同时锁定pf表(也就是父表)
应该是为了保证此时pf表的id=1记录不会被修改吧。
然后再去删除pf.id=1的记录可以正常删除了。
也就是说默认情况下,Oracle建外键的时候是不做级联删除的,而是抛出ORA-02292错误。
即存在子表关键值不能被删除。

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/12932950/viewspace-611452/,如需转载,请注明出处,否则将追究法律责任。

下一篇: Oracle 归档模式
请登录后发表评论 登录
全部评论

注册时间:2008-05-10

  • 博文量
    257
  • 访问量
    1022632