(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 record3
SQL> select * from T_CHILD1;
CHILD1_ID PARENT_ID
---------- ----------
1 1
SQL> select * from T_CHILD2;
CHILD2_ID PARENT_ID
---------- ----------
2 2
SQL> 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 record
found
SQL> 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;
