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),
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
In response to
sfpug by date
|Next:||From: Dror Matalon||Date: 2003-04-04 19:28:02|
|Subject: Bloki, a new Zapatec service|
|Previous:||From: Stephan Szabo||Date: 2003-04-04 06:06:23|
|Subject: Re: Changing a foreign key constraint?|