From: | Keith Keller <kkeller-postgres(at)wombat(dot)san-francisco(dot)ca(dot)us> |
---|---|
To: | SF Postgres <sfpug(at)postgresql(dot)org> |
Subject: | Re: Changing a foreign key constraint? |
Date: | 2003-04-04 06:22:56 |
Message-ID: | 20030404062255.GA1936@wombat.san-francisco.ca.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | sfpug |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Dror Matalon | 2003-04-04 19:28:02 | Bloki, a new Zapatec service |
Previous Message | Stephan Szabo | 2003-04-04 06:06:23 | Re: Changing a foreign key constraint? |