Oracle 外來鍵的級聯處理

昨天在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錯誤。

即存在子表關鍵值不能被刪除。