Re: ad hoc referential integrity

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.