Re: ON DELETE CASCADE (was: Modifying an existing

on 6/4/04 7:14 PM, Stephan Szabo at sszabo(at)megazone(dot)bigpanda(dot)com wrote:

> On Fri, 4 Jun 2004, Lynna Landstreet wrote:
>
>> Does anyone know if it's possible to modify an existing table constraint -
>> for example, to add "ON DELETE CASCADE" to an existing foreign key
>> constraint? Or would I have to recreate the table to add that?
>
> You can drop the constraint and add it again with the additional clause
> using ALTER TABLE. One warning is that it will recheck the constraint in
> this case which may make the ALTER slow if there's alot of data.

Cool - tried it, and it worked. Thanks!

Now, just to make sure I'm applying this correctly: ON DELETE CASCADE tells
the table you're applying it to a foreign key within to delete a row if the
corresponding row in another table which the foreign key references is
deleted, right?

So if I have a table of artists, and a table of exhibitions, and a join
table linking them by specifying which artists have appeared in which
exhibitions, and I add ON DELETE CASCADE to both the artist_id and
exhibition_id columns in the join table, then if either an artist or an
exhibition is deleted, any rows in the join table that reference that artist
will be deleted automagically? It won't go one step further and delete the
exhibition or artist it was joining them to, will it?

Just wanting to make sure I'm not setting myself up for disaster here...

Lynna

--
Resource Centre Database Coordinator
Gallery 44
www.gallery44.org