Oracle数据库中外键的相关操作整理

racle使用外键来限制子表中参考的字段值，要求子表中的数据必须在主表中存在。当主表的记录发生变化时导致外键参考唯一约束值发生了变化时，oracle指定了三种动作：默认值（类似于restrict）、delete cascade和delete set null。(
1.创建父表并初始化数据

sql> create table t_parent (parent_id int primary key, name varchar2(10));
table created.
sql> insert into t_parent values (1,'record1');
1 row created.
sql> insert into t_parent values (2,'record2');
1 row created.
sql> insert into t_parent values (3,'record3');
1 row created.
sql> commit;
commit complete.

2.创建三种类型的子表t_child1、t_child2和t_child3
(1)no action类别

sql> create table t_child1 (child1_id int primary key, parent_id int);
table created.
sql> alter table t_child1 add constraint fk_t_child1 foreign key (parent_id) references t_parent (parent_id);
table altered.
sql> insert into t_child1 values (1,1);
1 row created.
sql> commit;
commit complete.

(2)cascade类型

sql> create table t_child2 (child2_id int primary key, parent_id int);
table created.
sql> alter table t_child2 add constraint fk_t_child2 foreign key (parent_id) references t_parent (parent_id) on delete cascade;
table altered.
sql> insert into t_child2 values (2,2);
1 row created.
sql> commit;
commit complete.

(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;


1 row deleted.

sql> select * from t_child3;


 child2_id parent_id
---------- ----------
     3

  主表记录可以完成删除，子表中对应的内容被设置为null。