Disabling Constraints : An Automated ProcessDisabling 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
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
SELECT 'ALTER TABLE ' || a.table_name || '
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
if cur_fk%rowcount = 0 then
FEEL FREE TO COPY AND USE THE ABOVE CODE FOR DISABLING CONSTRAINTS, BUT REMEMBER: USE AT OWN RISK...
Free eBookSubscribe 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.Read more Testimonials
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."
Theory & Practice
SQL Server DBA
Install SQL Server
Database Normalization eBook:
Copyright © www.databasedesign-resource.com /
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
The name Oracle is a trademark of Oracle Corporation.