Home » SQL & PL/SQL » SQL & PL/SQL » Disable All constraints in single SQL statement...
Disable All constraints in single SQL statement... [message #199329] Mon, 23 October 2006 10:30 Go to next message
Messages: 24
Registered: February 2005
Location: Hyderabad
Junior Member
Hi All,

I need to diable all constraints in my table.
Using single SQL statement, will it be possible..
I know how to disable each constraint in table.

But my table is so big and it has many constraints.

So, could any body provide the syntax to disable constaints in table with single SQL statemet...

Awaiting your response.

Thanks & Regards,

Babu SRSB.
Re: Disable All constraints in single SQL statement... [message #199342 is a reply to message #199329] Mon, 23 October 2006 12:25 Go to previous messageGo to next message
Messages: 20970
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Spool something like this into a file and then execute it; as there's no a single SQL statement to disable all constraints on a table, I'm afraid you'll have to help yourself with such a query.
        uc.table_name ||
        uc.constraint_name   || ' ;'
FROM user_constraints uc, 
     user_tables ut
WHERE uc.table_name = ut.table_name
  AND ut.table_name = 'EMP';
Re: Disable All constraints in single SQL statement... [message #199491 is a reply to message #199329] Tue, 24 October 2006 14:53 Go to previous message
Messages: 74
Registered: July 2002
CREATE OR REPLACE PROCEDURE p_disable_constraint(p_table_name IN VARCHAR)
CURSOR c_disable_constraint
select constraint_name from all_constraints
where table_name = p_table_name;
FOR I in c_disable_constraint
EXECUTE IMMEDIATE 'ALTER TABLE ' || p_table_name || ' DISABLE CONSTRAINT ' || i.constraint_name;

And execute the procedure with table name.

[Updated on: Tue, 24 October 2006 15:00]

Report message to a moderator

Previous Topic: Designing tables and queries for performance
Next Topic: An initial blank line in a spool file
Goto Forum:

Current Time: Tue Jan 24 17:41:49 CST 2017

Total time taken to generate the page: 0.08660 seconds