Re: ON DELETE CASCADE (was: Modifying an existing

On Sat, 5 Jun 2004, Lynna Landstreet wrote:

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

Yep.

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

Correct. If there's any similar behavior you do want (for example not
allowing exhibitions with no artists or some such) you'll pretty much need
to do custom triggers.