Comment ajouter des contraintes "on delete cascade" ?

Question

Dans PostgreSQL 8.4.9, est-il possible d'ajouter "on delete cascades" aux deux clés étrangères de la table suivante sans supprimer la 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)

Les deux tableaux référencés figurent ci-dessous :

# \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)

Et je me demande également s'il est judicieux d'ajouter deux index à la première table ?

UPDATE : Merci, et j'ai également reçu l'avis de la liste de diffusion, selon lequel je pourrais gérer tout cela en un seul relevé et donc ne pas avoir besoin d'une transaction :

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;

Answer 1

Je suis presque sûr que vous ne pouvez pas simplement ajouter on delete cascade à une contrainte de clé étrangère existante. Vous devez d'abord supprimer la contrainte, puis ajouter la version correcte. En SQL standard, je crois que la façon la plus simple de procéder est la suivante

• lancer une transaction,
• abandonne la clé étrangère,
• ajouter une clé étrangère avec on delete cascade et enfin
• valider la transaction

Répétez l'opération pour chaque clé étrangère que vous souhaitez modifier.

Mais PostgreSQL possède une extension non standard qui vous permet d'utiliser plusieurs clauses de contraintes dans une seule instruction SQL. Par exemple

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;

Si vous ne connaissez pas le nom de la contrainte de clé étrangère que vous voulez supprimer, vous pouvez soit la rechercher dans pgAdminIII (il suffit de cliquer sur le nom de la table et de regarder la DDL, ou de développer la hiérarchie jusqu'à ce que vous voyez "Contraintes"), ou vous pouvez interroger le schéma d'information .

select *
from information_schema.key_column_usage
where position_in_unique_constraint is not null