 | |
Re: Modifying an existing constraint?
comp.databases.postgresql.novice comp.databases.postgresql.novice
 |
| | Thread Tools | Display Modes |
#1
 
| |||
| |||
|
Re: Modifying an existing constraint? -
06-04-2004
, 06:14 PM
|
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? |
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.
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo (AT) postgresql (DOT) org)
#2
| |||
| |||
|
|
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. |
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
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo (AT) postgresql (DOT) org
#3
| |||
| |||
|
|
on 6/4/04 7:14 PM, Stephan Szabo at sszabo (AT) megazone (DOT) 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? |
Quote:
|
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? |
allowing exhibitions with no artists or some such) you'll pretty much need
to do custom triggers.
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo (AT) postgresql (DOT) org)
|
« Previous Thread
|
Next Thread »
| Thread Tools | |
| Display Modes | |
Linear Mode
| |
Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.