1 建表
SQL> create table t_p (id number, name varchar2(30));
Table created
SQL> alter table t_p add primary key (id);
Table altered
SQL> create table t_c (id number, fid number, name varchar2(30));
Table created
SQL> alter table t_c add constraint fk_t_c foreign key(fid) references t_p(id);
Table altered
SQL> insert into t_p values (1, 'a');
1 row inserted
SQL> insert into t_p values (2, 'b');
1 row inserted
SQL> insert into t_c values (1, 1, 'a');
1 row inserted
SQL> commit;
Commit complete
2 on delete no action
SQL> delete t_p where id = 1;
delete t_p where id = 1
ORA-02292: 违反完整约束条件 (MYHR.FK_T_C) - 已找到子记录
SQL> UPDATE T_P SET ID = 3 WHERE ID = 1;
UPDATE T_P SET ID = 3 WHERE ID = 1
ORA-02292: 违反完整约束条件 (MYHR.FK_T_C) - 已找到子记录
3 on delete set null
SQL> alter table t_c drop constraint fk_t_c;
Table altered
SQL> alter table t_c add constraint fk_t_c foreign key (fid) references t_p (id) on delete set null;
Table altered
SQL> delete t_p where id = 1;
1 row deleted
SQL> commit;
Commit complete
SQL> select * from t_p;
ID NAME
---------- ------------------------------
2 b
SQL> select * from t_c;
ID FID NAME
---------- ---------- ------------------------------
1 a
方格内为空
4 on delete cascade
SQL> alter table t_c drop constraint fk_t_c;
Table altered
SQL> alter table t_c add constraint fk_t_c foreign key (fid) references t_p (id) on delete cascade;
Table altered
SQL> delete t_p where id = 1;
1 row deleted
SQL> select * from t_p;
ID NAME
---------- ------------------------------
2 b
SQL> select * from t_c;
ID FID NAME
---------- ---------- ------------------------------
记录被级联删除