Postgres Pro
Downloads
Home   >   mailing lists

Thread: altering foreign keys

altering foreign keys

From
"Adam Rich"
Date:
In my database, I have a core table that nearly all other tables
key against.  Now I need to adjust all of those foreign keys to
add a "on update cascade" action.  Is there a way to alter the
existing keys? (it didn't jump out at me in the manual)

If not, is there a serious issue preventing this feature?

If I have to drop and re-create all of the foreign keys, is it
possible to wrap the whole operation in a transaction without
risking invalid inserts in the referring tables? (I come from
an Oracle background, where DDL causes an implicit commit)

Re: altering foreign keys

From
Erik Jones
Date:
On Jan 22, 2008, at 1:11 PM, Adam Rich wrote:

> In my database, I have a core table that nearly all other tables
> key against.  Now I need to adjust all of those foreign keys to
> add a "on update cascade" action.  Is there a way to alter the
> existing keys? (it didn't jump out at me in the manual)
>
> If not, is there a serious issue preventing this feature?
>
> If I have to drop and re-create all of the foreign keys, is it
> possible to wrap the whole operation in a transaction without
> risking invalid inserts in the referring tables? (I come from
> an Oracle background, where DDL causes an implicit commit)

DDL in Postgres is transactional so your second option will work just
fine.

Erik Jones

DBA | Emma®
erik@myemma.com
800.595.4401 or 615.292.5888
615.292.0777 (fax)

Emma helps organizations everywhere communicate & market in style.
Visit us online at http://www.myemma.com

Re: altering foreign keys

From
Richard Broersma Jr
Date:
--- On Tue, 1/22/08, Adam Rich <adam.r@sbcglobal.net> wrote:

> Is there a way to alter the
> existing keys? (it didn't jump out at me in the manual)

    ALTER TABLE your_table
DROP CONSTRAINT your_column_fkey_constraint,
 ADD CONSTRAINT your_column_fkey_constraint
    FOREIGN KEY your_column
     REFERENCES parent_table( primary_column )
      ON UPDATE CASCADE;

Regards,
Richard Broersma Jr.

Re: altering foreign keys

From
"Adam Rich"
Date:
> In my database, I have a core table that nearly all other tables
> key against.  Now I need to adjust all of those foreign keys to
> add a "on update cascade" action.  Is there a way to alter the
> existing keys? (it didn't jump out at me in the manual)
>

Would it be possible to modify confupdtype in pg_constraint ?