Enabling Constraints- Tables and Constraints

This section contains a few scripts to help you enable constraints that you’ve disabled. Listed next is a script that creates a file with the SQL statements required to re-enable any foreign key constraints for tables owned by a specified user:

set lines 132 trimsp on head off feed off verify off echo off pagesize 0 spo enable_dyn.sql select ‘alter table ‘ || a.table_name || ‘ enable 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;

When enabling constraints, by default, Oracle checks to ensure that the data does not violate the constraint definition. If you are fairly certain that the data integrity is fine and that you do not need to incur the performance hit by revalidating the constraint, you can use the NOVALIDATE clause when re-enabling the constraints. Here is an example:

SQL> select ‘alter table ‘ || a.table_name || ‘ modify constraint ‘ || a.constraint_name || ‘ enable novalidate;’ 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’);

The NOVALIDATE clause instructs Oracle not to validate the constraints being enabled, but it does enforce that any new DML activities adhere to the constraint definition.

In multiuser systems, the possibility exists that another session has inserted data into the child table while the foreign key constraint was disabled. If that happens, you see the following error when you attempt to re-enable the foreign key:

ORA-02298: cannot validate (<owner>.<constraint>) – parent keys not found

In this scenario, you can use the ENABLE NOVALIDATE clause:

SQL> alter table emp enable novalidate constraint emp_dept_fk;

To clean up the rows that violate the constraint, first ensure that you have an EXCEPTIONS table created in your currently connected schema. If you do not have an EXCEPTIONS table, use this script to create one:

SQL> @?/rdbms/admin/utlexcpt.sql

Next, populate the EXCEPTIONS table with the rows that violate the constraint, using the EXCEPTIONS INTO clause:

SQL> alter table emp modify constraint emp_dept_fk validate exceptions into exceptions;

This statement still throws the ORA-02298 error as long as there are rows that violate the constraint. The statement also inserts records into the EXCEPTIONS table for any bad rows. You can now use the ROW_ID column of the EXCEPTIONS table to remove any records that violate the constraint.

Here, you see that one row needs to be removed from the EMP table:

SQL> select * from exceptions;

Here is some sample output:

ROW_ID        OWNER      TABLE_NAME CONSTRAINT

—————— ——– ———- ——————–AAAFKQAABAAAK8JAAB MV_MAINT EMP                                                    EMP_DEPT_FK

To remove the offending record, issue a DELETE statement:

SQL> delete from emp where rowid = ‘AAAFKQAABAAAK8JAAB’;

If the EXCEPTIONS table contains many records, you can run a query such as the following to delete by OWNER and TABLE_NAME:

SQL> delete from emp where rowid in (select row_id from exceptions where owner=upper(‘&owner’) and table_name = upper(‘&table_name’));

You may also run into situations in which you need to disable primary key or unique key constraints, or both. For instance, you may want to perform a large data load and for performance reasons want to disable the primary key and unique key constraints. You do not want to incur the overhead of having every row checked as it is inserted.

The same general techniques used for disabling foreign keys are applicable for disabling primary and unique keys. Run this query to display the primary key and unique key constraints for a user:

SQL> select a.table_name ,a.constraint_name ,a.constraint_type from dba_constraints a where a.owner = upper(‘&table_owner’) and a.constraint_type in (‘P’,’U’) order by a.table_name;

When the table name and constraint name are identified, use the ALTER TABLE statement to disable the constraint:

SQL> alter table dept disable constraint dept_pk;

Note Oracle does not let you disable a primary key or unique key constraint that is referenced in an enabled foreign key constraint.You first have to disable the foreign key constraint.