Skip site navigation (1) Skip section navigation (2)

Re: Find all foreign keys and dropping them from a script.

From: George Young <gry(at)ll(dot)mit(dot)edu>
To: pgsql-novice(at)postgresql(dot)org
Subject: Re: Find all foreign keys and dropping them from a script.
Date: 2006-04-18 17:59:00
Message-ID: 20060418135900.4d917931.gry@ll.mit.edu (view raw or flat)
Thread:
Lists: pgsql-novice
On Tue, 18 Apr 2006 16:05:48 +0200
"Robert Landsmeer" <r(dot)landsmeer(at)wis(dot)nl> wrote:
> I am having a hard time finding all foreign keys on my database trough a
> script and then removing them.
> 
> The reason I need to do this is that the application I am using has data
> that has been altered within an update of the product.
> 
> To update the database we have a script that will execute all the data
> updates of the product. Since the updater isn't aware of what updates
> need to be done before other updates FK-wise we get all kinds of errors
> about incorrect data, and the script fails.  So we want to drop all
> foreign keys, insert a lot of data, and then re-add all foreign keys. We
> already have a script that re-adds all foreign keys and also adds the
> new foreign keys for the new product release.

Try:
   select c.conname, cl.relname
      from pg_constraint c, pg_class cl
      where cl.oid=c.conrelid and c.contype='f';

There should be a way to get this from the more portable/standard 'information_schema' schema, but I can't seem to find it right now...

-- George Young 

> So at the moment I am looking for a way to get all current FK's trough a
> JDBC connection. Since a script will auto generate the drop key scripts
> it would be nice to have a way to do this. But after some searching on
> the internet I could not find a nice way of doing this (if I have missed
> a resource, please feel free to point me to it :-) ).
> 
>  
> 
> Thanks in advance,
> 
> Robert
> 
> 


-- 
"Are the gods not just?"  "Oh no, child.
What would become of us if they were?" (CSL)

In response to

pgsql-novice by date

Next:From: Tom LaneDate: 2006-04-18 19:26:44
Subject: Re: The COPY command and csv files
Previous:From: Eric Matthew FinninDate: 2006-04-18 17:58:34
Subject: Re: The COPY command and csv files

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group