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.

 

 

 

 

著作權聲明：本文為博主原創文章，未經博主允許不得轉載。

oracle 主外鍵管理