外键的主要作用是,保证外键列包含在父亲的主键列内。
存在外键的主键无法禁用
alter table t1 disable constraint pk;
alter table t2 disable constraint fk01;
alter table t1 disable constraint pk;
删除主表的记录时,你必先删除子表的记录才能够删主表的记录
删除子表的记录时,你可以直接删除
ORACLE 数据库支持级联删除,但要看你的约束是怎么建的
1.ON DELETE CASCADE (级联删除)
2.ON DELETE SET NULL (删除主表,子表列设置NULL)
没有设置上面两种方式,缺省是不允许删除,必须遵循下面的方式
删除主表的记录时,你必先删除子表的记录才能够删主表的记录
删除子表的记录时,你可以直接删除.
#################查询TAI对应的所有儿子表#########################
select DISTINCT T1.TABLE_NAME AS "父表名称",
T1.constraint_name AS "父表主键约束",
T1.constraint_type,
T.OWNER,
T.TABLE_NAME,
T.constraint_name,
T.constraint_type,
T.STATUS
from dba_constraints T /*---子表信息*/, dba_constraints T1 /*父表信息*/
where T.owner = 'INASDB'
AND T.r_constraint_name = T1.constraint_name
AND T.constraint_type = 'R'
AND T1.TABLE_NAME = 'TAI'
测试案例1ON DELETE CASCADE
select * from t1;
insert into t2 select * from t1 where rownum<1000;
alter table t1 add constraint pk primary key(object_name)
alter table t2 rename column object_name to object_namexx;
alter table t2 DROP constraint fk01;
alter table t2 add constraint fk01 foreign key(object_namexx) references t1(object_name) ON DELETE CASCADE;
select * from dba_constraints where table_name='T1'
UNION ALL
select * from dba_constraints where table_name='T2'
清理父亲表数据:
TRUNCATE TABLE T1;
delete from T1;
测试案例1默认 no action
测试案例1默认 set null
alter table t2 DROP constraint fk01;
alter table t2 add constraint fk01 foreign key(object_namexx) references t1(object_name) on delete set null;