Re: ON DELETE CASCADE (was: Modifying an existing - Mailing list pgsql-novice

From Stephan Szabo
Subject Re: ON DELETE CASCADE (was: Modifying an existing
Date
Msg-id 20040605235309.G80334@megazone.bigpanda.com
Whole thread Raw
In response to Re: ON DELETE CASCADE (was: Modifying an existing  (Lynna Landstreet <lynna@gallery44.org>)
List pgsql-novice
On Sat, 5 Jun 2004, Lynna Landstreet wrote:

> on 6/4/04 7:14 PM, Stephan Szabo at sszabo@megazone.bigpanda.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.


pgsql-novice by date:

Previous
From: Lynna Landstreet
Date:
Subject: Re: ON DELETE CASCADE (was: Modifying an existing
Next
From: "Ilya S. Slyzhnyak"
Date:
Subject: wikipedia article