pgAdmin: Dropping all foreign key constraints at once

Questions and answers regarding general SQL and backend databases

pgAdmin: Dropping all foreign key constraints at once

Postby huber » Mon Feb 25, 2013 7:33 pm

Hi All

How can I drop all foreign key at once in pgAdmin. As I found no way to select them all, I can't drop them (all). This must be possible with the worlds most advanced database ;-) As dropping them table by table is not a very efficient way. What am I missing? Thanks for pointing me to the right path.

Regards, Robert
Robert Huber
7r AG, Switzerland
SAN Developer
http://www.seven-r.ch
User avatar
huber
 
Posts: 516
Joined: Mon May 14, 2012 11:31 pm

Re: pgAdmin: Dropping all foreign key constraints at once

Postby ROCLASI » Mon Feb 25, 2013 7:59 pm

Hi Robert,

What are you trying to do exactly ? Why do you want/need to drop the constraints ?
If you need to defer your constraints until, lets say, the end of transaction you can use the set constraints command.
Dropping a constraint can only be done by altering a table. So you need to do this per table. I am not sure pgAdmin (or any other DBA tool for that matter) has a function to select multiple tables and do this.
Robert Ivens
SAN Developer / Servoy Valued Professional / Servoy Certified Developer

ROCLASI Software Solutions / JBS Group, Partner
Mastodon: @roclasi
--
ServoyForge - Building Open Source Software.
PostgreSQL - The world's most advanced open source database.
User avatar
ROCLASI
Servoy Expert
 
Posts: 5438
Joined: Thu Oct 02, 2003 9:49 am
Location: Netherlands/Belgium

Re: pgAdmin: Dropping all foreign key constraints at once

Postby huber » Mon Feb 25, 2013 11:39 pm

Hi Robert

I try to get rid of all of my constraints. I do not go into details but it has to do with parsing the SBB (Swiss federal railways) timetable. And I was as usual when I get back to pgAdmin from SybaseCentral a bit annoyed about the lack functionality (compared to SybaseCentral).
Same is true if you like to get rid of all indexes etc. Why? Because for me it's easy to generate out of the data model all constraints or indexes and apply them, quick and easy in SybaseCentral, time consuming in pgAdmin. I know you don't like to hear that, but there are so many more things with pgAdmin, we can discuss them may be next time at ServoyCamp :-) On the other hand, Postgres was at a time the only (Open Source) database handling GIS (PostGIS) which we needed. Today it's a different world.
Thumb up for pgAdmin it looks much better as SybaseCentral .-)

Regards, Robert

ROCLASI wrote:Hi Robert,

What are you trying to do exactly ? Why do you want/need to drop the constraints ?
If you need to defer your constraints until, lets say, the end of transaction you can use the set constraints command.
Dropping a constraint can only be done by altering a table. So you need to do this per table. I am not sure pgAdmin (or any other DBA tool for that matter) has a function to select multiple tables and do this.
Robert Huber
7r AG, Switzerland
SAN Developer
http://www.seven-r.ch
User avatar
huber
 
Posts: 516
Joined: Mon May 14, 2012 11:31 pm

Re: pgAdmin: Dropping all foreign key constraints at once

Postby ROCLASI » Tue Feb 26, 2013 12:05 pm

Hi Robert,

huber wrote:I know you don't like to hear that, but there are so many more things with pgAdmin, we can discuss them may be next time at ServoyCamp :-)

Not sure why you would say that. I know PostgreSQL and/or it's ecosystem are not perfect. No product is. But it's getting better and better and the developers are listening and are open for input from users like you and me.
I am not involved with the development of pgAdmin but I do know a few of the developers who do. I suggest you register with the pgadmin-support (or even the pgadmin-hackers) mailing list and voice your concerns and feature requests. They can't read minds so you have to tell them what you need in your use-cases.
Also check out the archives of the mailing lists because your question might already have been answered before.

Hope this helps.
Robert Ivens
SAN Developer / Servoy Valued Professional / Servoy Certified Developer

ROCLASI Software Solutions / JBS Group, Partner
Mastodon: @roclasi
--
ServoyForge - Building Open Source Software.
PostgreSQL - The world's most advanced open source database.
User avatar
ROCLASI
Servoy Expert
 
Posts: 5438
Joined: Thu Oct 02, 2003 9:49 am
Location: Netherlands/Belgium

Re: pgAdmin: Dropping all foreign key constraints at once

Postby huber » Tue Feb 26, 2013 5:27 pm

Hi Robert

I wrote it also with an eye blink :-) But sometimes it sounds as if Postgres and it's tools would be so good. Like when the change from Sybase to Postgres occurred - take that, it's that great. I sometimes wish these discussions would be a bit more comparative and a bit less absolute. But may be I am at the moment just a bit too much surrounded by people touting Open Source as a religion, as if the mere mentioning of Open Source automatically would make a good product.
Thanks for suggesting the URL's, but that is just also the problem - making suggestion and explaining is very time consuming. And to me, selecting all indices or selecting all foreign keys in an so called interactive tool is not that far streched - just daily business, so a bit useless to mention, as very obvious, isn't it. Sorry if I reacted a bit annoyed, was not meant that way.

Best regards, Robert


ROCLASI wrote:Hi Robert,

huber wrote:I know you don't like to hear that, but there are so many more things with pgAdmin, we can discuss them may be next time at ServoyCamp :-)

Not sure why you would say that. I know PostgreSQL and/or it's ecosystem are not perfect. No product is. But it's getting better and better and the developers are listening and are open for input from users like you and me.
I am not involved with the development of pgAdmin but I do know a few of the developers who do. I suggest you register with the pgadmin-support (or even the pgadmin-hackers) mailing list and voice your concerns and feature requests. They can't read minds so you have to tell them what you need in your use-cases.
Also check out the archives of the mailing lists because your question might already have been answered before.

Hope this helps.
Robert Huber
7r AG, Switzerland
SAN Developer
http://www.seven-r.ch
User avatar
huber
 
Posts: 516
Joined: Mon May 14, 2012 11:31 pm


Return to SQL Databases

Who is online

Users browsing this forum: No registered users and 6 guests