racle使用外鍵來限制子表中參考的字段值，要求子表中的數據必須在主表中存在。當主表的記錄發生變化時導致外鍵參考唯一約束值發生了變化時，Oracle指定了三種動作：默認值（類似于restrict）、delete cascade和delete set null。(
1.創建父表并初始化數據
SQL> create table t_parent (parent_id int primary key, name varchar2(10));Table created.SQL> insert into t_parent values (1,'record1');1 row created.SQL> insert into t_parent values (2,'record2');1 row created.SQL> insert into t_parent values (3,'record3');1 row created.SQL> commit;Commit complete.
2.創建三種類型的子表t_child1、t_child2和t_child3
(1)no action類別
SQL> create table t_child1 (child1_id int primary key, parent_id int);Table created.SQL> alter table t_child1 add constraint FK_t_child1 foreign key (parent_id) references t_parent (parent_id);Table altered.SQL> insert into t_child1 values (1,1);1 row created.SQL> commit;Commit complete.
(2)cascade類型
SQL> create table t_child2 (child2_id int primary key, parent_id int);Table created.SQL> alter table t_child2 add constraint FK_t_child2 foreign key (parent_id) references t_parent (parent_id) on delete cascade;Table altered.SQL> insert into t_child2 values (2,2);1 row created.SQL> commit;Commit complete.
(3)SET NULL類型
SQL> create table t_child3 (child2_id int primary key, parent_id int);Table created.SQL> alter table t_child3 add constraint FK_t_child3 foreign key (parent_id) references t_parent (parent_id) on delete set null;Table altered.SQL> insert into t_child3 values (3,3);1 row created.SQL> commit;Commit complete.
3.確認主表和子表中的數據
SQL> select * from T_PARENT;PARENT_ID NAME---------- ---------- 1 record1 2 record2 3 record3SQL> select * from T_CHILD1;CHILD1_ID PARENT_ID---------- ---------- 1 1SQL> select * from T_CHILD2; CHILD2_ID PARENT_ID---------- ---------- 2 2SQL> select * from T_CHILD3; CHILD2_ID PARENT_ID---------- ---------- 3 3
4.嘗試對具有默認類型外鍵參照的主表記錄進行刪除
SQL> delete from T_PARENT where parent_id = 1;delete from T_PARENT where parent_id = 1*ERROR at line 1:ORA-02292: integrity constraint (HBHE.FK_T_CHILD1) violated - child recordfoundSQL> select * from T_CHILD1; CHILD1_ID PARENT_ID---------- ---------- 1 1
在此類型下，不允許刪除操作
5.嘗試對具有delete cascade類型外鍵參照的主表記錄進行刪除
SQL> delete from T_PARENT where parent_id = 2;1 row deleted.SQL> select * from T_CHILD2;no rows selected
級聯刪除成功
6.嘗試對具有delete set null類型外鍵參照的主表記錄進行刪除
SQL> delete from T_PARENT where parent_id = 3;1 row deleted.SQL> select * from T_CHILD3; CHILD2_ID PARENT_ID---------- ---------- 3
主表記錄可以完成刪除，子表中對應的內容被設置為NULL。
