oracle的六种约束,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

2on 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) -已找到子记录

3on 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;

IDFID NAME

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

1a

方格内为空

4on 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;

IDFID NAME

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

记录被级联删除