Comment append des contraintes “on delete cascade”?

Dans PostgreSQL 8, est-il possible d’append ON DELETE CASCADES aux deux clés étrangères dans le tableau suivant sans supprimer ce dernier?

 # \d scores Table "public.scores" Column | Type | Modifiers ---------+-----------------------+----------- id | character varying(32) | gid | integer | money | integer | not null quit | boolean | last_ip | inet | Foreign-key constraints: "scores_gid_fkey" FOREIGN KEY (gid) REFERENCES games(gid) "scores_id_fkey" FOREIGN KEY (id) REFERENCES users(id) 

Les deux tables référencées sont ci-dessous – ici:

 # \d games Table "public.games" Column | Type | Modifiers ----------+-----------------------------+---------------------------------------------------------- gid | integer | not null default nextval('games_gid_seq'::regclass) rounds | integer | not null finished | timestamp without time zone | default now() Indexes: "games_pkey" PRIMARY KEY, btree (gid) Referenced by: TABLE "scores" CONSTRAINT "scores_gid_fkey" FOREIGN KEY (gid) REFERENCES games(gid) 

Et ici:

 # \d users Table "public.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: "users_pkey" PRIMARY KEY, btree (id) Referenced by: TABLE "cards" CONSTRAINT "cards_id_fkey" FOREIGN KEY (id) REFERENCES users(id) TABLE "catch" CONSTRAINT "catch_id_fkey" FOREIGN KEY (id) REFERENCES users(id) TABLE "chat" CONSTRAINT "chat_id_fkey" FOREIGN KEY (id) REFERENCES users(id) TABLE "game" CONSTRAINT "game_id_fkey" FOREIGN KEY (id) REFERENCES users(id) TABLE "hand" CONSTRAINT "hand_id_fkey" FOREIGN KEY (id) REFERENCES users(id) TABLE "luck" CONSTRAINT "luck_id_fkey" FOREIGN KEY (id) REFERENCES users(id) TABLE "match" CONSTRAINT "match_id_fkey" FOREIGN KEY (id) REFERENCES users(id) TABLE "misere" CONSTRAINT "misere_id_fkey" FOREIGN KEY (id) REFERENCES users(id) TABLE "money" CONSTRAINT "money_id_fkey" FOREIGN KEY (id) REFERENCES users(id) TABLE "pass" CONSTRAINT "pass_id_fkey" FOREIGN KEY (id) REFERENCES users(id) TABLE "payment" CONSTRAINT "payment_id_fkey" FOREIGN KEY (id) REFERENCES users(id) TABLE "rep" CONSTRAINT "rep_author_fkey" FOREIGN KEY (author) REFERENCES users(id) TABLE "rep" CONSTRAINT "rep_id_fkey" FOREIGN KEY (id) REFERENCES users(id) TABLE "scores" CONSTRAINT "scores_id_fkey" FOREIGN KEY (id) REFERENCES users(id) TABLE "status" CONSTRAINT "status_id_fkey" FOREIGN KEY (id) REFERENCES users(id) 

Et puis je me demande s’il est logique d’append 2 index’es à l’ancien tableau?

MISE À JOUR: Merci, et j’ai aussi le conseil à la liste de diffusion, que je pourrais le gérer dans 1 déclaration et donc sans démarrer explicitement une transaction:

 ALTER TABLE public.scores DROP CONSTRAINT scores_gid_fkey, ADD CONSTRAINT scores_gid_fkey FOREIGN KEY (gid) REFERENCES games(gid) ON DELETE CASCADE; 

    Je suis certain que vous ne pouvez pas simplement append une on delete cascade à une contrainte de clé étrangère existante. Vous devez d’abord supprimer la contrainte, puis append la version correcte. En standard SQL, je pense que la façon la plus simple de le faire est de

    • lancer une transaction,
    • laisser tomber la clé étrangère,
    • append une clé étrangère avec une on delete cascade , et enfin
    • valider la transaction

    Répétez 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 contrainte dans une seule instruction SQL. Par exemple

     alter table public.scores drop constraint scores_gid_fkey, add constraint scores_gid_fkey foreign key (gid) references games(gid) on delete cascade; 

    Si vous ne connaissez pas le nom de la contrainte de clé étrangère à supprimer, vous pouvez la rechercher dans pgAdminIII (cliquez simplement sur le nom de la table et examinez le DDL ou développez la hiérarchie jusqu’à ce que “Contraintes” apparaisse). ou vous pouvez interroger le schéma d’information .

     select * from information_schema.key_column_usage where position_in_unique_constraint is not null 

    Usage:

     select replace_foreign_key('user_rates_posts', 'post_id', 'ON DELETE CASCADE'); 

    Fonction:

     CREATE OR REPLACE FUNCTION replace_foreign_key(f_table VARCHAR, f_column VARCHAR, new_options VARCHAR) RETURNS VARCHAR AS $$ DECLARE constraint_name varchar; DECLARE reftable varchar; DECLARE refcolumn varchar; BEGIN SELECT tc.constraint_name, ccu.table_name AS foreign_table_name, ccu.column_name AS foreign_column_name FROM information_schema.table_constraints AS tc JOIN information_schema.key_column_usage AS kcu ON tc.constraint_name = kcu.constraint_name JOIN information_schema.constraint_column_usage AS ccu ON ccu.constraint_name = tc.constraint_name WHERE constraint_type = 'FOREIGN KEY' AND tc.table_name= f_table AND kcu.column_name= f_column INTO constraint_name, reftable, refcolumn; EXECUTE 'alter table ' || f_table || ' drop constraint ' || constraint_name || ', ADD CONSTRAINT ' || constraint_name || ' FOREIGN KEY (' || f_column || ') ' || ' REFERENCES ' || reftable || '(' || refcolumn || ') ' || new_options || ';'; RETURN 'Constraint replaced: ' || constraint_name || ' (' || f_table || '.' || f_column || ' -> ' || reftable || '.' || refcolumn || '); New options: ' || new_options; END; $$ LANGUAGE plpgsql; 

    Sachez que cette fonction ne copie pas les atsortingbuts de la clé étrangère initiale. Il ne prend que le nom de la table étrangère / le nom de la colonne, supprime la clé actuelle et la remplace par la nouvelle.