Home » SQL & PL/SQL » SQL & PL/SQL » How to drop all constraints on a schema at once?
How to drop all constraints on a schema at once? [message #275006] Thu, 18 October 2007 01:53 Go to next message
hedonist123
Messages: 119
Registered: August 2007
Senior Member
Hi,

Is there any single command or query in oracle to drop all the foreign keys on all tables on the schema at once? Does it also work for primary keys?
Re: How to drop all constraints on a schema at once? [message #275011 is a reply to message #275006] Thu, 18 October 2007 02:09 Go to previous messageGo to next message
dhananjay
Messages: 635
Registered: March 2002
Location: Mumbai
Senior Member
hi,

you can create a script to drop all the constraints.search this forum you might come across many examples.


regards,
Re: How to drop all constraints on a schema at once? [message #275012 is a reply to message #275011] Thu, 18 October 2007 02:11 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
No, there's no single command. The mechanism is similar to dropping all tables of a single user: you fetch the constraint names trough user/all/dba_constraints and let SQL generate the script for you.

MHE
Re: How to drop all constraints on a schema at once? [message #275013 is a reply to message #275006] Thu, 18 October 2007 02:12 Go to previous messageGo to next message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member

spool it and run,

select 'alter table '|| u.table_name ||' drop constraint ' ||c.constraint_name  
 from user_tables u ,user_constraints c 
where u.table_name =c.table_name ;

[Updated on: Thu, 18 October 2007 02:13]

Report message to a moderator

Re: How to drop all constraints on a schema at once? [message #275016 is a reply to message #275013] Thu, 18 October 2007 02:14 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Arju wrote on Thu, 18 October 2007 09:12

spoonfeed it and run,
Very Happy

MHE
Re: How to drop all constraints on a schema at once? [message #275017 is a reply to message #275013] Thu, 18 October 2007 02:18 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
2 remarks:
- I don't see the relevance of user_tables
- You didn't limit to foreign key

Regards
Michel
Re: How to drop all constraints on a schema at once? [message #275020 is a reply to message #275017] Thu, 18 October 2007 02:21 Go to previous messageGo to next message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member

Michel Cadot wrote on Thu, 18 October 2007 13:18

2 remarks:
- I don't see the relevance of user_tables
- You didn't limit to foreign key

Regards
Michel



Yes, It's left for the OP.
Re: How to drop all constraints on a schema at once? [message #275073 is a reply to message #275020] Thu, 18 October 2007 04:05 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Or you can remove the reference to user_tables entirely, and have this:
select 'alter table '|| c.table_name ||' drop constraint ' ||c.constraint_name||';'  
 from user_constraints c;


It'll even have trailing ';' on the commands, so that SQL*Plus will execute them
Re: How to drop all constraints on a schema at once? [message #275079 is a reply to message #275006] Thu, 18 October 2007 04:14 Go to previous messageGo to next message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member

Also,it didn't limit to foreign key. http://www.orafaq.com/forum/images/message_icons/icon12.gif
Re: How to drop all constraints on a schema at once? [message #275095 is a reply to message #275079] Thu, 18 October 2007 04:46 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Hey - I'm just improving your script, not spoonfeeding the O
Re: How to drop all constraints on a schema at once? [message #275166 is a reply to message #275020] Thu, 18 October 2007 11:47 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Arju wrote on Thu, 18 October 2007 09:21


Yes, It's left for the OP.

That's a lame excuse.
Either you provide a script that is complete or you tell the original poster he should "try something along the lines of.."
..or you make a mistake, which is perfectly ok, and don't make an excuse for it.

[Updated on: Thu, 18 October 2007 11:48]

Report message to a moderator

Previous Topic: pl/sql cursors
Next Topic: Index rebuild error
Goto Forum:
  


Current Time: Sat Dec 03 16:28:54 CST 2016

Total time taken to generate the page: 0.12522 seconds