CASCADE DELETE just once

postgres delete cascade not working
postgres delete row and all references
sql server cascade delete multiple foreign keys
postgres alter table on delete cascade
on update cascade w3schools
delete on delete cascade postgres
not null on delete cascade
on update cascade mysql

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.

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;

How to use the PostgreSQL DELETE CASCADE, Now you have to define the basic schema only once and inherit from it. You still have a common PK column for all ratings with a single attached  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.

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.

Documentation: 8.2: Constraints, However, each room belongs to one only one building. A room would not exist without a building. The relationship between the buildings and rooms tables is one-  Use the ON DELETE CASCADE option if you want rows deleted from the child table when the DELETE or MERGE statement removes corresponding rows from the parent table.

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;

Cascade deletes from multiple tables, If a record in the parent table is deleted, then the corresponding records in the child The syntax for creating a foreign key with cascade delete using a CREATE  Cascade delete is commonly used in database terminology to describe a characteristic that allows the deletion of a row to automatically trigger the deletion of related rows.

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.

MySQL ON DELETE CASCADE: Deleting Data from Related Tables , You can see how the previous example is much simpler as it only requires one DELETE statement. Imagine if we had 5 or 6 tables with foreign  You are describing the documented behaviour for cascade delete when the foreign key is nullable: If a foreign key on the dependent entity is nullable, Code First does not set cascade delete on the relationship, and when the principal is deleted the foreign key will be set to null.

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.

SQL Server: Foreign Keys with cascade delete, If a system failure occurs and only one transaction occurs, then the database CASCADE: Delete or update the row from the parent table and automatically  Cascading delete refers to the action of removing records associated by a foreign key relationship to a record that is the target of a deletion action. LINQ to SQL does not specifically handle cascading deletes and it is up to the developer to determine whether or not that action is desired.

Using DELETE CASCADE Option for Foreign Keys, In the INSERT and UPDATE specifications, select Cascade for the delete rule Once you click on Yes, a foreign key with delete rule is created. Cascade delete would just go ahead and delete the child records resulting in loss of information about orders for instance if you deleted a customer who had orders in the past. This sort of thing can thoroughly mess up your financial records.

PostgreSQL: Transactions and Foreign Keys, If an ON UPDATE CASCADE or ON DELETE CASCADE subclause is only defined for one FOREIGN KEY clause, cascading operations fail with an error. Note. A foreign key with cascade delete means that if a record in the parent table is deleted, then the corresponding records in the child table will automatically be deleted. This is called a cascade delete in SQL Server. A foreign key with cascade delete can be created using either a CREATE TABLE statement or an ALTER TABLE statement.

DELETE CASCADE and UPDATE CASCADE in SQL Server foreign , REFERENCES is the key word used for a FOREIGN KEY constraint (which allows to cascade DELETE or UPDATE). Your database design seems to have logical flaws. rating seems like a detail of the main table restaurant. Since you have a 1:1 relationship, you could just include the "rating" columns in the main table.

Comments
  • Please see my custom function below. It's possible with certain restrictions.
  • This doesn't necessarily work as there could be other foreign keys cascading from the original cascading (recursion). You can even get into a loop where table a refers to b which refers to a. To achieve this in a general sense, see my table below, but it has some restrictions. If you have a simple table setup then try the code above, it's easier to comprehend what you're doing.
  • Simple, safe. You should run them in a single transaction if you have density inserts.
  • clearly "a few cascading deletes"≠dropping all data from the table…
  • 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... this is potentially very dangerous.
  • beware. this is a reckless answer.
  • Somebody has flagged this answer for deletion - presumably because they disagree with it. The correct course of action in that case is to downvote, not flag.
  • He has the warning on top. If you choose to ignore that, no one can help you. I think your "copyPaste" users are the real danger here.
  • It happens all the time especially with self referencing tables. Consider a company with different management tiers in different departments, or a generic hierarchical taxonomy. Yes, I agree that this function is not the absolute best thing since sliced bread, but it's a useful tool in the right situation.
  • If you rewrite it accept array of IDs and also generate queries which will use IN operator with sub-selects instead of = (so step to use sets logic) it would became much faster.
  • Thank you for your solution. I write some tests and I needed to delete a record and I was having trouble to cascade that delete. Your function worked really well!
  • @JoeLove what speed problem are you have? In that situation recursion is single correct solution on my mind.