Disabling Constraints- Tables and Constraints

One nice feature of Oracle is that you can disable and enable constraints without dropping and re-creating them. This means you avoid having to know the DDL statements that would be required to re-create the dropped constraints.

Occasionally, you need to disable constraints. For example, you may be trying to truncate a table but receive the following error message:

ORA-02266: unique/primary keys in table referenced by enabled foreign keys Oracle does not allow a truncate operation on a parent table with a primary key that is referenced by an enabled foreign key in a child table. If you need to truncate a parent table, you first have to disable all the enabled foreign key constraints that reference the parent table’s primary key. Run this query to determine the names of the constraints that need to be disabled:

SQL> col primary_key_table form a18

SQL> col primary_key_constraint form a18 SQL> col fk_child_table form a18

SQL> col fk_child_table_constraint form a18 —

SQL> select

b.table_name primary_key_table ,b.constraint_name primary_key_constraint ,a.table_name fk_child_table ,a.constraint_name fk_child_table_constraint from dba_constraints a,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;

Do not 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 does not 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 re-enable the constraints, you have to query the data dictionary to determine which constraints have been disabled and then re-enable them individually.

Sometimes, you run into situations when loading data in which it is convenient to disable all the foreign keys before loading the data. In these situations, the impdbutility imports the tables in alphabetical order and does not ensure that child tables are imported before parent tables. You may also want to run several Data Pump import jobs in parallel to take advantage of parallel hardware. In such scenarios, you can disable the foreign keys, perform the import, and then re-enable 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 and a.constraint_type = ‘R’

and b.owner = upper(‘&table_owner’); spo off;

This script generates a file, named dis_dyn.sql, which contains the SQL statements to disable all the foreign key constraints for a user.