外键, on delete cascade和on delete set null

外键

create table t1
(c1 number(10) primary key);

create table t2
(c1 number(10) references t1(c1),
c2 varchar2(10));

T2 表的 C1 字段与 T1 表的 C1 字段建立了外键关系。

T1 叫父表(parent table). T2 叫子表(child table).

先在 T1 表里插入一条数据：

insert into t1 values(123);
commit;

然后在 T2 里尝试插入一条数据：

insert into t2 values(456);

由于 T2 表与 T1 表有外键关系，且 T1 表里没有 456 这条数据，所以报错了：ORA-02291

insert into t2 values(123);
commit;

这样就好了。

尝试删除 T1 里的数据：

delete from t1;

由于T1表只有一条数据，所以没有指定where子句。

子表 T2 引用了父表 T1 里的内容，不能直接删除父表里的数据。

需要先删除子表 T2 里对应的数据：

delete from t2;
delete from t1;
commit;
select * from t1;
select * from t2;

扩展：on delete cascade参数

创建两张表：

T3是父表，T4是子表，子表的外键上指定了on delete cascade参数

create table t3
(c1 number(10),
constraint t3_pk primary key(c1));

create table t4
(c1 number(10),
c2 varchar2(10),
constraint t4_fk foreign key(c1) references t3(c1) on delete cascade);

各插入两条数据：

insert into t3 values(123);
insert into t3 values(456);
insert into t4 values(123,'ABC');
insert into t4 values(456,'DEF');
commit;

这里我们直接删除父表 T3 里的数据：

delete from t3 where c1=123;
commit;

可以看到能直接删除父表里的数据，但是子表里对应的一行数据也被删掉了。 这就是 on delete cascade 参数的作用

扩展：on delete set null 参数

创建两张表：

T5是父表，T6是子表，子表的外键上指定了on delete set null参数

create table t5
(c1 number(10),
constraint t5_pk primary key(c1));

create table t6
(c1 number(10),
c2 varchar2(10),
constraint t6_fk foreign key(c1) references t5(c1) on delete set null);

各插入两条数据：

insert into t5 values(123);
insert into t5 values(456);
insert into t6 values(123,'ABC');
insert into t6 values(456,'DEF');
commit;

我们直接删除父表 T5 里的数据：

delete from t5 where c1=123;
commit;

可以看到能直接删除父表里的数据，但是子表里对应字段的数据变成空值(NULL)了。 这就是 on delete set null 参数的作用

如果父表没有定义主键会怎样？

比如：

create table t7 (c1 number(10));

create table t8
(c1 number(10),
c2 varchar2(10),
constraint t8_fk foreign key(c1) references t7(c1));

提示没有找到唯一或主键约束 ，加个唯一约束看看：

alter table t7 add constraint t7_uk unique(c1);

-- 再执行建表语句
create table t8
(c1 number(10),
c2 varchar2(10),
constraint t8_fk foreign key(c1) references t7(c1));

成功了。

进阶：

我们在字段上定义主键约束或唯一约束时，oracle会在该字段创建一个唯一索引。

如何查看？

select ui.table_name,ucc.column_name,ui.index_name,
       uc.constraint_name,ui.uniqueness 
from user_indexes ui 
join user_constraints uc on(ui.index_name=uc.index_name) 
join user_cons_columns ucc on(ucc.constraint_name=uc.constraint_name); 

1. 可以看到索引名和约束名是一致的。

2. 创建 T1 表时指定了主键约束，但是没有给约束起名，SYS_C0025178 是系统给的约束/索引名。

3. T1, T3, T5 创建时加了主键约束，而 T7 是创建后加了唯一约束。

那么唯一索引能不能替代唯一约束或主键约束呢？？？

create table t9 (c1 number(10));

create unique index t9_uidx on t9(c1);

create table t10
(c1 number(10),
c2 varchar2(10),
constraint t10_fk foreign key(c1) references t9(c1));

看来不行。

select distinct ui.table_name,ucc.column_name,uic.column_name,
       ui.index_name,uc.constraint_name,ui.uniqueness 
from user_indexes ui 
left join user_constraints uc on(ui.index_name=uc.index_name) 
left join user_cons_columns ucc on(ucc.constraint_name=uc.constraint_name)
left join user_ind_columns uic on(ucc.column_name=uic.column_name) 
order by ui.table_name;

不过在一些场景下唯一索引确实可以替代主键约束或唯一约束。

比如，GoldenGate 同步时如果找不到表上的主键就会去找该表上的唯一索引 。