昨天在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 > select * from pf;
ID
----------
1
2
3
4
20:59:41 > select * from pc;
ID PARENT_ID
---------- ----------
1 1
2 3
4 3
5 2
6 1
20:59:44 > 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 > delete from pf where id = 1;
1 row deleted.
20:59:58 > 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 > commit;
Commit complete.
21:00:06 > 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 > select * from pf;
ID
----------
2
3
4
21:00:12 > select * from pc;
ID PARENT_ID
---------- ----------
2 3
4 3
5 2
21:00:15 >
这就引出了一个问题,默认的情况是否是级联删除的呢?做个实验:
21:16:43 > !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 > 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 > delete from pc2 where parent_id = 1;
2 rows deleted.
21:19:00 > col object_name format A10;
21:19:28 > 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 > 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错误。
即存在子表关键值不能被删除。