Thread: Modifying an existing constraint?
Hi there, 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? Lynna -- Resource Centre Database Coordinator Gallery 44 www.gallery44.org
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.
Lynna Landstreet <lynna@gallery44.org> writes:
> 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?
There's no command to modify a foreign key constraint like that, but you
should be able to drop and re-add that single constraint using ALTER TABLE.
I don't see a reason to recreate the whole table...
regards, tom lane
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? 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
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.