OraFAQ Forum: SQL & PL/SQL » Disable All constraints in single SQL statement...

Home » SQL & PL/SQL » SQL & PL/SQL » Disable All constraints in single SQL statement...

Show: Today's Messages :: Show Polls :: Message Navigator
E-mail to friend

Disable All constraints in single SQL statement... [message #199329]

Mon, 23 October 2006 10:30

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.

Report message to a moderator

Re: Disable All constraints in single SQL statement... [message #199342 is a reply to message #199329]

Mon, 23 October 2006 12:25

Littlefoot
Messages: 21127
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';

Report message to a moderator

Re: Disable All constraints in single SQL statement... [message #199491 is a reply to message #199329]

Tue, 24 October 2006 14:53

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:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Jul 22 00:01:22 CDT 2017

Total time taken to generate the page: 0.09396 seconds