OraFAQ Forum: SQL & PL/SQL » How to drop all constraints on a schema at once?

Home » SQL & PL/SQL » SQL & PL/SQL » How to drop all constraints on a schema at once?

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

How to drop all constraints on a schema at once? [message #275006]

Thu, 18 October 2007 01:53

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?

Report message to a moderator

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

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,

Report message to a moderator

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

Maaher
Messages: 7065
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

Report message to a moderator

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

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

Maaher
Messages: 7065
Registered: December 2001

Senior Member

Arju wrote on Thu, 18 October 2007 09:12

spoonfeed it and run,

MHE

Report message to a moderator

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

Michel Cadot
Messages: 68770
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

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

Regards
Michel

Report message to a moderator

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

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.

Report message to a moderator

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

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

Report message to a moderator

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

Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...

Senior Member

Also,it didn't limit to foreign key.

Report message to a moderator

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

JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...

Senior Member

Hey - I'm just improving your script, not spoonfeeding the O

Report message to a moderator

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

Frank
Messages: 7901
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:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Sep 04 14:12:34 CDT 2025