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
sidagam
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
Littlefoot
Messages: 20900
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.
SELECT 'ALTER TABLE ' || 
        uc.table_name ||
      ' DISABLE CONSTRAINT ' ||
        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
jigar
Messages: 74
Registered: July 2002
Member
CREATE OR REPLACE PROCEDURE p_disable_constraint(p_table_name IN VARCHAR)
AS
CURSOR c_disable_constraint
IS
select constraint_name from all_constraints
where table_name = p_table_name;
BEGIN
FOR I in c_disable_constraint
LOOP
EXECUTE IMMEDIATE 'ALTER TABLE ' || p_table_name || ' DISABLE CONSTRAINT ' || i.constraint_name;
END LOOP;
END;

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: Wed Dec 07 20:10:57 CST 2016

Total time taken to generate the page: 0.12185 seconds