Databases Reference
In-Depth Information
,dba_constraints b
where a.r_constraint_name = b.constraint_name
and a.r_owner = b.owner
and a.constraint_type = 'R'
and b.owner = upper('&table_owner')
and b.table_name = upper('&pk_table_name');
For this example, there is only one foreign key dependency:
PRIMARY_KEY_TAB PRIMARY_KEY_CON FK_CHILD_TABLE FK_CHILD_TABLE_
--------------- --------------- --------------- ---------------
DEPT DEPT_PK EMP EMP_DEPT_FK
Use the
ALTER TABLE
statement to disable constraints on a table. In this case, there is only one foreign key to
disable:
SQL> alter table emp disable constraint emp_dept_fk;
You can now truncate the parent table:
SQL> truncate table dept;
Don't forget to reenable the foreign key constraints after the truncate operation has completed, like this:
SQL> alter table emp enable constraint emp_dept_fk;
You can disable a primary key and all dependent foreign key constraints with the
CASCADE
option of the
DISABLE
clause. For example, the next line of code disables all foreign key constraints related to the primary key constraint:
SQL> alter table dept disable constraint dept_pk cascade;
This statement doesn't cascade through all levels of dependencies; it only disables the foreign key constraints
directly dependent on
DEPT_PK
. Also keep in mind that there is no
ENABLE...CASCADE
statement. To reenable the
constraints, you have to query the data dictionary to determine which constraints have been disabled and then
reenable them individually.
Sometimes, you run into situations, when loading data, in which it's convenient to disable all the foreign keys
before loading the data (perhaps from a schema-level import, using the
imp
utility). In these situations the
imp
utility
imports the tables in alphabetical order and doesn't ensure that child tables are imported before parent tables.
You may also want to run several import jobs in parallel to take advantage of parallel hardware. In such scenarios,
you can disable the foreign keys, perform the import, and then reenable the foreign keys.
Here is a script that uses SQL to generate SQL to disable all foreign key constraints for a user:
set lines 132 trimsp on head off feed off verify off echo off pagesize 0
spo dis_dyn.sql
select 'alter table ' || a.table_name
|| ' disable constraint ' || a.constraint_name || ';'
from dba_constraints a
,dba_constraints b
where a.r_constraint_name = b.constraint_name
and a.r_owner = b.owner
