屏蔽主外键 oracle,oracle 主外键治理

oracle 主外键管理

使用主外键约束使得数据具有完整性。

1、查询表上所有的约束

select * from user_constraints t

where t.table_name='FATHER';

2、查询具有主外键关系的表

select c.owner,c.constraint_name,c.constraint_type,c.table_name,f.owner,f.constraint_name,f.constraint_type,f.table_name

from dba_constraints c, dba_constraints f

where c.r_owner=f.owner

and c.r_constraint_name=f.constraint_name

and c.table_name='CHILD';  --查询子表CHILD对应的所有父表

3、子表中插入的记录必须在父表中存在，否则会报parent key not found

SQL> insert into child values ('datong',1);

insert into child values ('datong',1)

*

ERROR at line 1:

ORA-02291: integrity constraint (SCOTT.FK_ADDR) violated - parent key not found

4、父表的记录只有在子表中找不到才可以删除，否则会报child record found

SQL> delete from father where id=1;

delete from father where id=1

*

ERROR at line 1:

ORA-02292: integrity constraint (SCOTT.FK_ID) violated - child record found

SQL> delete from father where id=2;

1 row deleted.

SQL> commit;

Commit complete.

5、如何完全删除父表数据，如truncate、drop

SQL> truncate table father;

truncate table father

*

ERROR at line 1:

ORA-02266: unique/primary keys in table referenced by enabled foreign keys

针对上面情况，可以先将father表的所有子表的引用约束disable，使用下面的sql得到禁用子表约束语句：

select 'alter table '||c.owner||'.'||c.table_name||' modify constraint '||c.constraint_name||' disable;' "exec_sql"

from user_constraints c, user_constraints f

where c.r_owner=f.owner

and c.r_constraint_name=f.constraint_name

and f.table_name='FATHER';

exec_sql

-------------------------------------

alter table SCOTT.CHILD modify constraint FK_ID disable;

然后执行上面的查询结果，就可以禁掉所有的子表约束，truncate父表就不会报错了。

SQL> alter table SCOTT.CHILD modify constraint FK_ID disable;

Table altered.

SQL> truncate table father;

Table truncated.

当然，此时子表的引用约束不一定能起来(enable)，取决于子表是否有数据。

SQL> alter table SCOTT.CHILD modify constraint FK_ID enable;

alter table SCOTT.CHILD modify constraint FK_ID enable

*

ERROR at line 1:

ORA-02298: cannot validate (SCOTT.FK_ID) - parent keys not found

将子表数据全部删除，就可以起来(enable)子表的引用约束。

SQL> truncate table child;

Table truncated.

SQL> alter table SCOTT.CHILD modify constraint FK_ID enable;

Table altered.

SQL> drop table father;

drop table father

*

ERROR at line 1:

ORA-02449: unique/primary keys in table referenced by foreign keys

这种情况可以使用cascade constraints子句一同将子表的引用约束删掉。

SQL> drop table father cascade constraints;

Table dropped.

版权声明：本文为博主原创文章，未经博主允许不得转载。