Date: Mon, 14 Jul 2003 21:48:18 -0700 (PDT)
From: Stephan Szabo <sszabo@megazone.bigpanda.com>
To: Matt Tenenbaum <matt@tty1.org>
Cc: <pgsql-sql@postgresql.org>
Subject: Re: ad hoc referential integrity
In-Reply-To: <606FC028-B67E-11D7-BFF3-0003939515A2@tty1.org>
Message-ID: <20030714214641.B79686-100000@megazone.bigpanda.com>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII

On Tue, 15 Jul 2003, Matt Tenenbaum wrote:

> Say I created a couple tables A and B, where A contains a column p
> which B references as a foreign key. Further say that, when I created
> these tables, I meant to specify 'on delete cascade' for p in the
> definition of B but didn't. What's the proper way to ensure both the
> integrity of the tables and that, when I delete rows from A, the
> corresponding rows from B are removed as well? (In particular, is there
> a shorter way than dropping the table and recreating it? Are rules the
> answer?)

If I'm understanding what you want, I'd suggest dropping the constraint
and re-adding it with the on delete cascade.  If you're on 7.3 or better
you should be able to do this entirely with alter table, before that you
have to do a little more work.