Thread: How to add "on delete cascade" constraints
Hello,
in 8.4.9 is it please possible to add "on delete cascades" to
the both foreign keys in the following table w/o dropping the table?
# \d pref_scores
Table "public.pref_scores"
Column | Type | Modifiers
---------+-----------------------+-----------
id | character varying(32) |
gid | integer |
money | integer | not null
quit | boolean |
last_ip | inet |
Foreign-key constraints:
"pref_scores_gid_fkey" FOREIGN KEY (gid) REFERENCES pref_games(gid)
"pref_scores_id_fkey" FOREIGN KEY (id) REFERENCES pref_users(id)
There referenced tables are below:
# \d pref_games
Table "public.pref_games"
Column | Type | Modifiers
----------+-----------------------------+----------------------------------------------------------
gid | integer | not null default
nextval('pref_games_gid_seq'::regclass)
rounds | integer | not null
finished | timestamp without time zone | default now()
Indexes:
"pref_games_pkey" PRIMARY KEY, btree (gid)
Referenced by:
TABLE "pref_scores" CONSTRAINT "pref_scores_gid_fkey" FOREIGN KEY
(gid) REFERENCES pref_games(gid)
\d pref_users
Table "public.pref_users"
Column | Type | Modifiers
------------+-----------------------------+---------------
id | character varying(32) | not null
first_name | character varying(64) |
last_name | character varying(64) |
female | boolean |
avatar | character varying(128) |
city | character varying(64) |
login | timestamp without time zone | default now()
last_ip | inet |
logout | timestamp without time zone |
vip | timestamp without time zone |
mail | character varying(254) |
Indexes:
"pref_users_pkey" PRIMARY KEY, btree (id)
Referenced by:
TABLE "pref_cards" CONSTRAINT "pref_cards_id_fkey" FOREIGN KEY
(id) REFERENCES pref_users(id)
TABLE "pref_catch" CONSTRAINT "pref_catch_id_fkey" FOREIGN KEY
(id) REFERENCES pref_users(id)
TABLE "pref_chat" CONSTRAINT "pref_chat_id_fkey" FOREIGN KEY (id)
REFERENCES pref_users(id)
TABLE "pref_game" CONSTRAINT "pref_game_id_fkey" FOREIGN KEY (id)
REFERENCES pref_users(id)
TABLE "pref_hand" CONSTRAINT "pref_hand_id_fkey" FOREIGN KEY (id)
REFERENCES pref_users(id)
TABLE "pref_luck" CONSTRAINT "pref_luck_id_fkey" FOREIGN KEY (id)
REFERENCES pref_users(id)
TABLE "pref_match" CONSTRAINT "pref_match_id_fkey" FOREIGN KEY
(id) REFERENCES pref_users(id)
TABLE "pref_misere" CONSTRAINT "pref_misere_id_fkey" FOREIGN KEY
(id) REFERENCES pref_users(id)
TABLE "pref_money" CONSTRAINT "pref_money_id_fkey" FOREIGN KEY
(id) REFERENCES pref_users(id)
TABLE "pref_pass" CONSTRAINT "pref_pass_id_fkey" FOREIGN KEY (id)
REFERENCES pref_users(id)
TABLE "pref_payment" CONSTRAINT "pref_payment_id_fkey" FOREIGN KEY
(id) REFERENCES pref_users(id)
TABLE "pref_rep" CONSTRAINT "pref_rep_author_fkey" FOREIGN KEY
(author) REFERENCES pref_users(id)
TABLE "pref_rep" CONSTRAINT "pref_rep_id_fkey" FOREIGN KEY (id)
REFERENCES pref_users(id)
TABLE "pref_scores" CONSTRAINT "pref_scores_id_fkey" FOREIGN KEY
(id) REFERENCES pref_users(id)
TABLE "pref_status" CONSTRAINT "pref_status_id_fkey" FOREIGN KEY
(id) REFERENCES pref_users(id)
Regards
Alex
On 27/04/2012 19:59, Alexander Farber wrote: > Hello, > > in 8.4.9 is it please possible to add "on delete cascades" to > the both foreign keys in the following table w/o dropping the table? I think you will have to drop the foreign key and re-create it, but certainly not the table. Ray. -- Raymond O'Donnell :: Galway :: Ireland rod@iol.ie
So it's not a problem to drop and recreate the FOREIGN KEYs? And can I use START TRANSACTION while doing it? On Fri, Apr 27, 2012 at 9:30 PM, Raymond O'Donnell <rod@iol.ie> wrote: > On 27/04/2012 19:59, Alexander Farber wrote: >> in 8.4.9 is it please possible to add "on delete cascades" to >> the both foreign keys in the following table w/o dropping the table? > > I think you will have to drop the foreign key and re-create it, but > certainly not the table.
On 27/04/2012 20:40, Alexander Farber wrote: > So it's not a problem to drop and recreate the FOREIGN KEYs? No, unless you're doing it on a production system, and someone inserts a value that'll doesn't agree with the foreign key while it's gone. Adding the constraint back in may take a while (depending on how big your table is) as contents of the columns involved need to be checked to ensure they contain valid values. Also, I think maybe an exclusive lock is required, which will block out other users - not sure about this - others on this list will know. > And can I use START TRANSACTION while doing it? Yes, absolutely - transactional DDL is one of PG's great strengths. Ray. -- Raymond O'Donnell :: Galway :: Ireland rod@iol.ie
On Fri, Apr 27, 2012 at 12:40 PM, Alexander Farber
<alexander.farber@gmail.com> wrote:
> So it's not a problem to drop and recreate the FOREIGN KEYs?
>
> And can I use START TRANSACTION while doing it?
You could, but you don't need to since you can do all of this is one statement:
ALTER TABLE public.pref_scores
DROP CONSTRAINT pref_scores_gid_fkey,
ADD CONSTRAINT pref_scores_gid_fkey
FOREIGN KEY (gid)
REFERENCES pref_games(gid)
ON DELETE CASCADE;
--
Regards,
Richard Broersma Jr.
Thank you - this has worked perfectly On Fri, Apr 27, 2012 at 10:18 PM, Richard Broersma <richard.broersma@gmail.com> wrote: > You could, but you don't need to since you can do all of this is one statement: > > ALTER TABLE public.pref_scores > DROP CONSTRAINT pref_scores_gid_fkey, > ADD CONSTRAINT pref_scores_gid_fkey > FOREIGN KEY (gid) > REFERENCES pref_games(gid) > ON DELETE CASCADE;