Обсуждение: 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;