Thread: ad hoc referential integrity

At the outset I hope that this is the right place to ask this question 
(also, I hope the question isn't a foolish one).

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?)

Again, I hope this is the right place to answer this question; I guess 
I'm not 'new' to SQL, but I'm still a bit rough around the edges, so 
trying to find the answer on my own has been a bit slow-going.

Thanks for any help.
-matt

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.