The Database Design Resource Center



Disabling Constraints : An Automated Process

Disabling constraints: One of the advantages of Oracle over some competitors such as MS-SQL and Sybase is the ability to toggle a constraint or trigger on and off, without blatting it, and without the hassle of finding any code and any accessory information.

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


Exclusive interviews with:
Steven Feuerstein, PLSQL expert
Donald Burleson, Top IT consultant


Free eBook

Subscribe to my newsletter and get my ebook on Entity Relationship Modeling Principles as a free gift:


What visitors say...

"I just stumbled accross your site looking for some normalization theory and I have to say it is fantastic.

I have been in the database field for 10+ years and I have never before come across such a useful site. Thank you for taking the time to put this site together."

Mike, USA

Read more Testimonials



Database Normalization eBook:


Database Normalization eBook




Copyright © www.databasedesign-resource.com / Alf A. Pedersen
All rights reserved.
All information contained on this website is for informational purposes only.
Disclaimer: www.databasedesign-resource.com does not warrant any company, product, service or any content contained herein.

Return to top

Copyright acknowledgement note:

The name Oracle is a trademark of Oracle Corporation.
The names MS Access/MS SQL Server are trademarks of Microsoft Corporation.
Any other names used on this website may be trademarks of their respective owners, which I fully respect.