Enable / Disable Constraint

• by krishna
• December 2, 2009

Enabling and disabling Oracle constraints can be accomplished by using
1. Alter table with disable constraint command
2. Alter table with enable constraint command

For example, updates applied to a Parent Table may fail if the statement leaves orphaned rows in a child table, INSERT command against a Child Table may fail if a matching foreign key value does not exist in the parent table.

Constraint failures will result in the statement being rolled back – coding an application front end to deal with such errors is generally easier than handling all the business rules in code.

Enable Constraint
DECLARE
CURSOR constraint_cursor IS
SELECT table_name, constraint_name
FROM user_constraints
WHERE constraint_type = ‘R’
AND owner = UPPER( ‘${database.user}‘ );
BEGIN
FOR r IN constraint_cursor LOOP
execute immediate ‘ALTER TABLE ‘||r.table_name||’ ENABLE CONSTRAINT ‘||r.constraint_name;
END LOOP;
END;

Disable Constraint
DECLARE
CURSOR constraint_cursor IS
SELECT table_name, constraint_name
FROM user_constraints
WHERE constraint_type = ‘R’
AND owner = UPPER( ‘${database.user}‘ );
BEGIN
FOR r IN constraint_cursor LOOP
execute immediate ‘ALTER TABLE ‘||r.table_name||’ DISABLE CONSTRAINT ‘||r.constraint_name;
END LOOP;
END;

NOTE: ${database.user} need to be changed.

Enabling and disabling Oracle constraints can be accomplished by using 1. Alter table with disable constraint command 2. Alter table with enable constraint command For example, updates applied to a Parent Table may fail if the statement leaves orphaned rows in a child table, INSERT command against a Child Table may fail if a matching…