Oracle约束支持3种Delete的行为

Oracle约束支持3种Delete的行为

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

---------- ---------- ------------------------------

 

记录被级联删除