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

Re: Changing a foreign key constraint?

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 (view raw or flat)
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


In response to

sfpug by date

Next:From: Dror MatalonDate: 2003-04-04 19:28:02
Subject: Bloki, a new Zapatec service
Previous:From: Stephan SzaboDate: 2003-04-04 06:06:23
Subject: Re: Changing a foreign key constraint?

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