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

Re: Changing a foreign key constraint?

From: Stephan Szabo <sszabo(at)megazone23(dot)bigpanda(dot)com>
To: David Fetter <david(at)fetter(dot)org>
Cc: SF Postgres <sfpug(at)postgresql(dot)org>
Subject: Re: Changing a foreign key constraint?
Date: 2003-04-04 06:06:23
Message-ID: 20030403220325.P88717-100000@megazone23.bigpanda.com (view raw or flat)
Thread:
Lists: sfpug
On Thu, 3 Apr 2003, David Fetter wrote:

> Kind people,
>
> I'd like to change a foreign key constraint, 'cause it's not doing the
> right thing right now.
>
> Example DDL's below...
>
> CREATE TABLE foo (
>   foo_id SERIAL NOT NULL PRIMARY KEY
> , ...
> );
>
> CREATE TABLE bar (
> ...
> , foo_id INTEGER NOT NULL REFERENCES foo(foo_id)
> ...
> );
>
> I'd like to change that to
>
> , foo_id INTEGER NOT NULL REFERNCES foo(foo_id) ON DELETE CASCADE
>
> Is there any way to do this short of dropping & re-creating the db?
>
> Big TIA for any hints, tips or pointers on this :)

With 7.3, I think you can drop just the constraint and re-add it with the
additional thing.

something like:
ALTER TABLE bar DROP CONSTRAINT "$1";
ALTER TABLE bar ADD FOREIGN KEY (foo_id) REFERENCES foo(foo_id) ON DELETE
CASCADE;

In earlier versions, you could do this by finding and dropping the
constraint triggers for the constraint and then adding the constraint
again with alter table.


In response to

sfpug by date

Next:From: Keith KellerDate: 2003-04-04 06:22:56
Subject: Re: Changing a foreign key constraint?
Previous:From: David FetterDate: 2003-04-04 05:09:00
Subject: Changing a foreign key constraint?

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