Disabling Constraints : An Automated Process

Oracle automatically disable check constraints and referential constraints but Primary key, unique, and not null constraints remain in force until a disable command is used.

In oracle (SQL*Plus), a one line command can do a thing like this:

SELECT 'alter table '||table_name||' disable constraint
'||constraint_name||' cascade;'
FROM user_constraints /

Test it first on screen to view the result, and then use the SPOOL command to flush the result to a file, do a quick editing, and then run the file. This should give you a nice command file for disabling constraints.

To Disable 'anonymous' constraint:

CREATE TABLE foo (bar NUMBER, baz NUMBER, UNIQUE (bar, baz));

ALTER TABLE foo DISABLE UNIQUE (bar, baz);

To Disable named constraint:

CREATE TABLE foo (bar NUMBER, baz NUMBER, CONSTRAINT uq_foo UNIQUE (bar, baz));

ALTER TABLE foo DISABLE CONSTRAINT uq_foo;

To Disable a Check Constraint:

ALTER TABLE table_name DISABLE CONSTRAINT constraint_name;

In oracle, we can also use script for disabling constraints such as Primary key, unique, and not null:

SET PAGESIZE 0
SET FEEDBACK OFF
SET VERIFY OFF
SPOOL temp.sql

SELECT 'ALTER TABLE ' || a.table_name || '
DISABLE CONSTRAINT ' || a.constraint_name || ';'
FROM all_constraints a
WHERE a.constraint_type = 'C'
AND a.owner = Upper('&2');
AND a.table_name = DECODE(Upper('&1'),'ALL',a.table_name,UPPER('&1'));

SPOOL OFF

Disabling constraints can allow inconsistent data in your tables. This script is meant for use in testing and development environments only.

It was designed as part of a truncate and import process to add additional data to the testing or development environment.

REM User must have DBA role

set def on ver off
set serverout on size 1000000
prompt Enter table's owner
accept Tabowner prompt 'OWNER: '
prompt Enter comma separated uppercase table list: TAB1,TAB2,...
accept TabList prompt 'Table list: '
prompt Enter action ( ENABLE, DISABLE, ENABLE NOVALIDATE ... )
accept State prompt 'State: '

spool enable_disable_fk.log

declare
l_str_sel_cur varchar2(4000);
l_str_sql_ddl varchar2(4000);
l_owner dba_constraints.owner%TYPE;
l_constraint_name dba_constraints.constraint_name%TYPE;
l_table_name dba_constraints.table_name%TYPE;
type t_cur_fk is ref cursor;
cur_fk t_cur_fk;

begin
l_str_sel_cur:= 'select fk.owner, fk.constraint_name , fk.table_name ' ||
' from dba_constraints fk, dba_constraints pk ' ||
' where fk.CONSTRAINT_TYPE = ''R'' and ' ||
' pk.owner = upper(''&&Tabowner'') and ' ||
' fk.R_CONSTRAINT_NAME = pk.CONSTRAINT_NAME and ' ||
' pk.TABLE_NAME in ( ' ||
'''' || replace('&&TabList',',',''',''') ||
'''' || ')';
open cur_fk for l_str_sel_cur;
dbms_output.put_line('Executing: ');
loop
fetch cur_fk into l_owner, l_constraint_name, l_table_name;
exit when cur_fk%notfound;
l_str_sql_ddl := 'ALTER TABLE '||l_owner||'.'||
l_table_name||' MODIFY CONSTRAINT '||l_constraint_name||' &&State';

dbms_output.put_line(l_str_sql_ddl);
execute immediate l_str_sql_ddl;
end loop;

if cur_fk%rowcount = 0 then
dbms_output.put_line('No Foreign key to modify');
end if;
close cur_fk;
end;

FEEL FREE TO COPY AND USE THE ABOVE CODE FOR DISABLING CONSTRAINTS, BUT REMEMBER: USE AT OWN RISK...

Return to Oracle Database constraints