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.