使用主外鍵約束使得數據具有完整性。
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.
