1. No action on deletion or update（在删除或更新时候，没有任何动作.）
2.Cascading deletions（级联删除）
3. Deletions that set null（删除设置为空值）
[@more@]
1. No action on deletion or update（在删除或更新时候，没有任何动作.）
create table t3 (id int primary key,mname varchar2(20));
create table t4 (
xid number,
id int,
text varchar2(20),
constraint pk_xid primary key (xid),
constraint fk_id_t4 foreign key (id) references test.t3(id)
);
select owner,
constraint_name,
constraint_type,
r_owner,
r_constraint_name,
delete_rule
from dba_constraints
where constraint_name = 'FK_ID_T4';
OWNER CONSTRAINT_NAME CONSTRAINT_TYPE R_OWNER R_CONSTRAINT_NAME DELETE_RULE
-------------------------------------------------------------------------------------------------------------------------------------------
TEST FK_ID_T4 R TEST PK_ID_T3 NO ACTION
SQL>select * from t3;
ID MNAME
--- ----
1 aaa
2 ccc
3 ccc
4 eee
SQL> select * from t4;
XID ID TEXT
---- --------------------
10 1 xxxx
20 2 yyyy
30 3 xxxx
40 4 yyyy
SQL> update t3 set id=5 where id=4;
update t3 set id=5 where id=4
ORA-02292: 违反完整约束条件 (TEST.FK_ID_T4) - 已找到子记录
因为delete_rule 为NO ACTION，所以报错.
2.Cascading deletions（级联删除）
create table t3 (id int,mname varchar2(20),constraint pk_id_t3 primary key (id));
create table t4 (
xid number,
id int,
text varchar2(20),
constraint pk_xid primary key (xid),
constraint fk_id_t4 foreign key (id) references test.t3(id) on delete cascade
);
select owner,
constraint_name,
constraint_type,
r_owner,
r_constraint_name,
delete_rule
from dba_constraints
where constraint_name = 'FK_ID_T4';
OWNER CONSTRAINT_NAME CONSTRAINT_TYPE R_OWNER R_CONSTRAINT_NAME DELETE_RULE
-------------------------------------------------------------------------------------------------------------------------------------------
TEST FK_ID_T4 R TEST PK_ID_T3 CASCADE
SQL>select * from t3;
ID MNAME
--- ----
1 aaa
2 ccc
3 ccc
4 eee
SQL> select * from t4;
XID ID TEXT
---- --------------------
10 1 xxxx
20 2 yyyy
30 3 xxxx
40 4 yyyy
SQL> delete from t3 where id=4;
1 row deleted
SQL> commit;
Commit complete
再次查看:
SQL>select * from t3;
ID MNAME
--- ----
1 aaa
2 ccc
3 ccc
--父表记录已经被删除
SQL> select * from t4;
XID ID TEXT
---- --------------------
10 1 xxxx
20 2 yyyy
30 3 xxxx
--子表记录已经被级联删除
因为delete_rule的值为CASCADE.
注意，级联更新还是不行，人家oracle说过嘛，是“级联删除”，而非“级联更新”
SQL> update t3 set id=4 where id=3;
update t3 set id=4 where id=3
ORA-02292: 违反完整约束条件 (TEST.FK_ID_T4) - 已找到子记录
3. Deletions that set null（删除设置为空值）
create table t3 (id int,mname varchar2(20),constraint pk_id_t3 primary key (id));
create table t4 (
xid number,
id int,
text varchar2(20),
constraint pk_xid primary key (xid),
constraint fk_id_t4 foreign key (id) references test.t3(id) on delete set null
);
select owner,
constraint_name,
constraint_type,
r_owner,
r_constraint_name,
delete_rule
from dba_constraints
where constraint_name = 'FK_ID_T4';
OWNER CONSTRAINT_NAME CONSTRAINT_TYPE R_OWNER R_CONSTRAINT_NAME DELETE_RULE
-------------------------------------------------------------------------------------------------------------------------------------------
TEST FK_ID_T4 R TEST PK_ID_T3 SET NULL
SQL> delete from t3 where id=4;
1 row deleted
SQL> commit;
Commit complete
SQL>select * from t3;
ID MNAME
--- ----
1 aaa
2 ccc
3 ccc
--父表记录已经被删除
SQL> select * from t4;
XID ID TEXT
---- --------------------
10 1 xxxx
20 2 yyyy
30 3 xxxx
40 yyyy --子表记录已经被级联为空值.