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

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.

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.

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.

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.

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

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

SQL> delete from T_PARENT where parent_id = 2;
1 row deleted.
SQL> select * from T_CHILD2;
no rows selected

SQL> delete from T_PARENT where parent_id = 3;

1 row deleted.
SQL> select * from T_CHILD3;

 CHILD2_ID PARENT_ID
---------- ----------
   3