CASCADE DELETE just once

Question

I have a Postgresql database on which I want to do a few cascading deletes   However  the tables aren t set up with the ON DELETE CASCADE rule   Is there any way I can perform a delete and tell Postgresql to cascade it just this once   Something equivalent to  DELETE FROM some table CASCADE    The answers to this older question make it seem like no such solution exists  but I figured I d ask this question explicitly just to be sure

User · Answer

I cannot comment Palehorse s answer so I added my own answer  Palehorse s logic is ok but efficiency can be bad with big data sets   DELETE FROM some child table sct   WHERE exists  SELECT FROM some Table st                  WHERE sct some fk fiel st some id    DELETE FROM some table    It is faster if you have indexes on columns and data set is bigger than few records

User · Answer

The delete with the cascade option only applied to tables with foreign keys defined    If you do a delete  and it says you cannot because it would violate the foreign key constraint  the cascade will cause it to delete the offending rows   If you want to delete associated rows in this way  you will need to define the foreign keys first    Also  remember that unless you explicitly instruct it to begin a transaction  or you change the defaults  it will do an auto-commit  which could be very time consuming to clean up

User · Answer

I took Joe Love s answer and rewrote it using the IN operator with sub-selects instead of   to make the function faster  according to Hubbitus s suggestion    create or replace function delete cascade p schema varchar  p table varchar  p keys varchar  p subquery varchar default null  p foreign keys varchar   default array    varchar     returns integer as    declare      rx record      rd record      v sql varchar      v subquery varchar      v primary key varchar      v foreign key varchar      v rows integer      recnum integer   begin      recnum    0      select ccu column name into v primary key         from         information schema table constraints  tc         join information schema constraint column usage AS ccu ON ccu constraint name   tc constraint name and ccu constraint schema tc constraint schema         and tc constraint type  PRIMARY KEY          and tc table name p table         and tc table schema p schema       for rx in           select kcu table name as foreign table name           kcu column name as foreign column name           kcu table schema foreign table schema          kcu2 column name as foreign table primary key         from information schema constraint column usage ccu         join information schema table constraints tc on tc constraint name ccu constraint name and tc constraint catalog ccu constraint catalog and ccu constraint schema ccu constraint schema          join information schema key column usage kcu on kcu constraint name ccu constraint name and kcu constraint catalog ccu constraint catalog and kcu constraint schema ccu constraint schema         join information schema table constraints tc2 on tc2 table name kcu table name and tc2 table schema kcu table schema         join information schema key column usage kcu2 on kcu2 constraint name tc2 constraint name and kcu2 constraint catalog tc2 constraint catalog and kcu2 constraint schema tc2 constraint schema         where ccu table name p table  and ccu table schema p schema         and TC CONSTRAINT TYPE  FOREIGN KEY          and tc2 constraint type  PRIMARY KEY        loop         v foreign key    rx foreign table schema       rx foreign table name       rx foreign column name          v subquery     select     rx foreign table primary key     as key from    rx foreign table schema        rx foreign table name                  where     rx foreign column name    in    coalesce p keys  p subquery      for update           if p foreign keys   gt  ARRAY v foreign key  then             --raise notice  circular recursion detected           else             p foreign keys    array append p foreign keys  v foreign key               recnum   recnum   delete cascade rx foreign table schema  rx foreign table name  null  v subquery  p foreign keys               p foreign keys    array remove p foreign keys  v foreign key           end if      end loop       begin         if  coalesce p keys  p subquery   lt  gt      then             v sql     delete from    p schema        p table     where     v primary key    in    coalesce p keys  p subquery                    --raise notice     v sql              execute v sql              get diagnostics v rows   row count              recnum    recnum   v rows          end if          exception when others then recnum 0      end       return recnum   end     language PLPGSQL

User · Answer

No   To do it just once you would simply write the delete statement for the table you want to cascade   DELETE FROM some child table WHERE some fk field IN  SELECT some id FROM some Table   DELETE FROM some table

User · Answer

If you really want DELETE FROM some table CASCADE  which means  remove all rows from table some table   you can use TRUNCATE instead of DELETE and CASCADE is always supported  However  if you want to use selective delete with a where clause  TRUNCATE is not good enough   USE WITH CARE - This will drop all rows of all tables which have a foreign key constraint on some table and all tables that have constraints on those tables  etc   Postgres supports CASCADE with TRUNCATE command   TRUNCATE some table CASCADE    Handily this is transactional  i e  can be rolled back   although it is not fully isolated from other concurrent transactions  and has several other caveats  Read the docs for details

User · Answer

I wrote a  recursive  function to delete any row based on its primary key  I wrote this because I did not want to create my constraints as  on delete cascade   I wanted to be able to delete complex sets of data  as a DBA  but not allow my programmers to be able to cascade delete without thinking through all of the repercussions  I m still testing out this function  so there may be bugs in it -- but please don t try it if your DB has multi column primary  and thus foreign  keys  Also  the keys all have to be able to be represented in string form  but it could be written in a way that doesn t have that restriction  I use this function VERY SPARINGLY anyway  I value my data too much to enable the cascading constraints on everything  Basically this function is passed in the schema  table name  and primary value  in string form   and it will start by finding any foreign keys on that table and makes sure data doesn t exist-- if it does  it recursively calls itsself on the found data  It uses an array of data already marked for deletion to prevent infinite loops  Please test it out and let me know how it works for you  Note  It s a little slow  I call it like so  select delete cascade  public   my table   1     create or replace function delete cascade p schema varchar  p table varchar  p key varchar  p recursion varchar   default null   returns integer as    declare     rx record      rd record      v sql varchar      v recursion key varchar      recnum integer      v primary key varchar      v rows integer  begin     recnum    0      select ccu column name into v primary key         from         information schema table constraints  tc         join information schema constraint column usage AS ccu ON ccu constraint name   tc constraint name and ccu constraint schema tc constraint schema         and tc constraint type  PRIMARY KEY          and tc table name p table         and tc table schema p schema       for rx in           select kcu table name as foreign table name           kcu column name as foreign column name           kcu table schema foreign table schema          kcu2 column name as foreign table primary key         from information schema constraint column usage ccu         join information schema table constraints tc on tc constraint name ccu constraint name and tc constraint catalog ccu constraint catalog and ccu constraint schema ccu constraint schema          join information schema key column usage kcu on kcu constraint name ccu constraint name and kcu constraint catalog ccu constraint catalog and kcu constraint schema ccu constraint schema         join information schema table constraints tc2 on tc2 table name kcu table name and tc2 table schema kcu table schema         join information schema key column usage kcu2 on kcu2 constraint name tc2 constraint name and kcu2 constraint catalog tc2 constraint catalog and kcu2 constraint schema tc2 constraint schema         where ccu table name p table  and ccu table schema p schema         and TC CONSTRAINT TYPE  FOREIGN KEY          and tc2 constraint type  PRIMARY KEY        loop         v sql     select    rx foreign table primary key    as key from    rx foreign table schema       rx foreign table name                where    rx foreign column name       quote literal p key     for update           --raise notice     v sql          --found a foreign key  now find the primary keys for any data that exists in any of those tables          for rd in execute v sql         loop             v recursion key rx foreign table schema       rx foreign table name       rx foreign column name       rd key              if  v recursion key   any  p recursion   then                 --raise notice  Avoiding infinite loop               else                 --raise notice  Recursing to      rx foreign table name  rd key                  recnum   recnum  delete cascade rx foreign table schema  varchar  rx foreign table name  varchar  rd key  varchar  p recursion  v recursion key               end if          end loop      end loop      begin     --actually delete original record      v sql     delete from    p schema       p table    where    v primary key       quote literal p key       execute v sql      get diagnostics v rows  row count      --raise notice  Deleting          p schema p table v primary key p key      recnum   recnum  v rows      exception when others then recnum 0      end       return recnum  end     language PLPGSQL

User · Answer

You can use to automate this  you could define the foreign key constraint with ON DELETE CASCADE  I quote the the manual of foreign key constraints      CASCADE specifies that when a referenced row is deleted  row s    referencing it should be automatically deleted as well

User · Answer

If I understand correctly  you should be able to do what you want by dropping the foreign key constraint  adding a new one  which will cascade   doing your stuff  and recreating the restricting foreign key constraint   For example   testing   create table a  id integer primary key   NOTICE   CREATE TABLE   PRIMARY KEY will create implicit index  a pkey  for table  a  CREATE TABLE testing   create table b  id integer references a   CREATE TABLE  -- put some data in the table testing   insert into a values 1   INSERT 0 1 testing   insert into a values 2   INSERT 0 1 testing   insert into b values 2   INSERT 0 1 testing   insert into b values 1   INSERT 0 1  -- restricting works testing   delete from a where id 1  ERROR   update or delete on table  a  violates foreign key constraint  b id fkey  on table  b  DETAIL   Key  id   1  is still referenced from table  b    -- find the name of the constraint testing    d b         Table  public b   Column    Type     Modifiers  -------- --------- -----------  id       integer    Foreign-key constraints       b id fkey  FOREIGN KEY  id  REFERENCES a id   -- drop the constraint testing   alter table b drop constraint b a id fkey  ALTER TABLE  -- create a cascading one testing   alter table b add FOREIGN KEY  id  references a id  on delete cascade   ALTER TABLE  testing   delete from a where id 1  DELETE 1 testing   select   from a   id  ----   2  1 row   testing   select   from b   id  ----   2  1 row   -- it works  do your stuff  --  stuff   -- recreate the previous state testing    d b         Table  public b   Column    Type     Modifiers  -------- --------- -----------  id       integer    Foreign-key constraints       b id fkey  FOREIGN KEY  id  REFERENCES a id  ON DELETE CASCADE  testing   alter table b drop constraint b id fkey  ALTER TABLE testing   alter table b add FOREIGN KEY  id  references a id  on delete restrict   ALTER TABLE   Of course  you should abstract stuff like that into a procedure  for the sake of your mental health

User · Answer

Yeah  as others have said  there s no convenient  DELETE FROM my table     CASCADE   or equivalent   To delete non-cascading foreign key-protected child records and their referenced ancestors  your options include    Perform all the deletions explicitly  one query at a time  starting with child tables  though this won t fly if you ve got circular references   or Perform all the deletions explicitly in a single  potentially massive  query  or Assuming your non-cascading foreign key constraints were created as  ON DELETE NO ACTION DEFERRABLE   perform all the deletions explicitly in a single transaction  or  Temporarily drop the  no action  and  restrict  foreign key constraints in the graph  recreate them as CASCADE  delete the offending ancestors  drop the foreign key constraints again  and finally recreate them as they were originally  thus temporarily weakening the integrity of your data   or Something probably equally fun    It s on purpose that circumventing foreign key constraints isn t made convenient  I assume  but I do understand why in particular circumstances you d want to do it  If it s something you ll be doing with some frequency  and if you re willing to flout the wisdom of DBAs everywhere  you may want to automate it with a procedure    I came here a few months ago looking for an answer to the  CASCADE DELETE just once  question  originally asked over a decade ago    I got some mileage out of Joe Love s clever solution  and Thomas C  G  de Vilhena s variant   but in the end my use case had particular requirements  handling of intra-table circular references  for one  that forced me to take a different approach  That approach ultimately became recursively delete  PG 10 10    I ve been using recursively delete in production for a while  now  and finally feel  warily  confident enough to make it available to others who might wind up here looking for ideas  As with Joe Love s solution  it allows you to delete entire graphs of data as if all foreign key constraints in your database were momentarily set to CASCADE  but offers a couple additional features    Provides an ASCII preview of the deletion target and its graph of dependents  Performs deletion in a single query using recursive CTEs  Handles circular dependencies  intra- and inter-table  Handles composite keys  Skips  set default  and  set null  constraints

[postgresql] CASCADE DELETE just once

Examples related to postgresql