How to add on delete cascade constraints

Question

In PostgreSQL 8 is it possible to add ON DELETE CASCADES to the both foreign keys in the following table without dropping the latter      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    Both referenced tables are below - here      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    And here      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    And also I wonder if it makes sense to add 2 index es to the former table   UPDATE  Thank you  and also I ve got the advice at the mailing list  that I could manage it in 1 statement and thus without explicitly starting a transaction   ALTER TABLE public scores DROP CONSTRAINT scores gid fkey  ADD CONSTRAINT scores gid fkey    FOREIGN KEY  gid     REFERENCES games gid     ON DELETE CASCADE

User · Answer

Based off of  Mike Sherrill Cat Recall s answer  this is what worked for me   ALTER TABLE  Children  DROP CONSTRAINT  Children parentId fkey   ADD CONSTRAINT  Children parentId fkey    FOREIGN KEY   parentId     REFERENCES  Parent  id    ON DELETE CASCADE

User · Answer

Usage   select replace foreign key  user rates posts    post id    ON DELETE CASCADE      Function   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       - gt       reftable           refcolumn        New options       new options   END     LANGUAGE plpgsql    Be aware  this function won t copy attributes of initial foreign key  It only takes foreign table name   column name  drops current key and replaces with new one

User · Answer

I m pretty sure you can t simply add on delete cascade to an existing foreign key constraint   You have to drop the constraint first  then add the correct version  In standard SQL  I believe the easiest way to do this is to   start a transaction  drop the foreign key  add a foreign key with on delete cascade  and finally commit the transaction   Repeat for each foreign key you want to change   But PostgreSQL has a non-standard extension that lets you use multiple constraint clauses in a single SQL statement  For example  alter table public scores drop constraint scores gid fkey  add constraint scores gid fkey    foreign key  gid     references games gid     on delete cascade    If you don t know the name of the foreign key constraint you want to drop  you can either look it up in pgAdminIII  just click the table name and look at the DDL  or expand the hierarchy until you see  Constraints    or you can query the information schema   select   from information schema key column usage where position in unique constraint is not null

[postgresql] How to add "on delete cascade" constraints?

Examples related to postgresql

Examples related to constraints

Examples related to cascade

Examples related to cascading-deletes

Examples related to postgresql-8.4