Re: Changing a foreign key constraint?

On Thu, Apr 03, 2003 at 09:09:00PM -0800, David Fetter wrote:
> 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?

The *whole* DB?!? :)

If the foreign key had a name, you could do something like

ALTER TABLE DROP CONSTRAINT bar_foo_id_fkey;
ALTER TABLE ADD CONSTRAINT bar_foo_id_fkey FOREIGN KEY
(foo_id) REFERENCES foo (foo_id) ON DELETE CASCADE;

But it probably doesn't have a name (or has one like $1), so I'm
not sure if you can use ALTER TABLE.

Alternatively, you might be able to get away with changing the
appropriate system table. In 7.3 it's pg_constraint, I believe,
and in 7.2 it's pg_relcheck, I think. There are columns in
pg_constraint specifically for this behavior, but I don't
remember about pg_relcheck. The reference guide on system
tables has more information on these system tables.

HTNW (hope that's not wrong),

--keith

--
kkeller(at)speakeasy(dot)net
public key: http://wombat.san-francisco.ca.us/kkeller/kkeller.asc
alt.os.linux.slackware FAQ: http://wombat.san-francisco.ca.us/cgi-bin/fom