How does one write a DELETE CASCADE for postgres?

Question

I'm manually constructing a DELETE CASCADE statement for postgres.

I have a 'transaction' and a 'slice' table, related as shown below:

    Table "public.slice"
  Column  | Type | Modifiers 
----------+------+-----------
 id       | text | not null
 name     | text | 
Referenced by:
    TABLE "transaction" CONSTRAINT "transaction_slice_id_fkey" FOREIGN KEY (slice_id) REFERENCES slice(id)
 Table "public.transaction"
  Column  | Type | Modifiers 
----------+------+-----------
 id       | text | not null
 slice_id | text | 
Referenced by:
    TABLE "classification_item" CONSTRAINT "classification_item_transaction_id_fkey" FOREIGN KEY (transaction_id) REFERENCES transaction(id)
Table "public.classification_item"
     Column     | Type | Modifiers 
----------------+------+-----------
 id             | text | not null
 transaction_id | text | 
Foreign-key constraints:
    "classification_item_transaction_id_fkey" FOREIGN KEY (transaction_id) REFERENCES transaction(id)

Say I want to delete all transactions and classification_items referenced by the slice whose name is 'my_slice'. What do I need to write?

=# delete from classification_item where transaction_id= #...? 
=# delete from transaction where slice_id= #...? 
=# delete from slice where name='my_slice';

Suggestion: Improve the code block describing table structure. (I tried to improve this with an edit, but they rejected it …) With an entity-relationship diagram, it would save a lot of time to understand the table structure. Otherwise, just use consistent formatting, shorten lines to column width, and use whitespace lines to structure the block. The last two lines ("Foreign-key constraints") are redundant to one of the "Referenced by" sections. And in the intro you mention you have two tables, but you have three. — tanius
– tanius, Commented Dec 12, 2016 at 2:51

jira · Accepted Answer · 2010-09-14 19:58:01Z

54

Postgres foreign keys support the CASCADE deletes:

slice_id integer REFERENCES slice(id) ON DELETE CASCADE

etc

answered Sep 14, 2010 at 19:58

jira

3,9643 gold badges25 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Anti Veeranna Over a year ago

In other words, you dont write it, you just describe the relations and let Postgre handle the deletion.

Milen A. Radev · Accepted Answer · 2010-09-14 20:04:35Z

20

In case you can't do what others have suggested:

begin;
delete from classification_item where transaction_id in (select id from "transaction" where slice_id = (select id from slice where name = 'my_slice'));
delete from "transaction" where slice_id in (select id from slice where name='my_slice');
delete from slice where name='my_slice';
commit;

answered Sep 14, 2010 at 20:04

Milen A. Radev

63.2k22 gold badges111 silver badges112 bronze badges

2 Comments

AP257 Over a year ago

I can't; I've inherited fairly complex code that uses SQLAlchemy. This is ideal, thank you.

hraban Over a year ago

sqlalchemy supports on delete cascade: docs.sqlalchemy.org/en/latest/orm/cascades.html -- it's not this postgres directive exactly, but it's similar and might be better than doing this by hand

tanius · Accepted Answer · 2016-12-11 15:50:03Z

You should use CASCADE deletes, and it should be possible to do so even if you inherited a database schema. You would just modify the constraints to add the CASCADE deletes to the schemas:

Drop and re-create the constraints to add CASCADE deletes:

ALTER TABLE ONLY "transaction" 
    DROP CONSTRAINT transaction_slice_id_fkey;
ALTER TABLE ONLY "transaction" 
    ADD CONSTRAINT transaction_slice_id_fkey 
    FOREIGN KEY (slice_id) REFERENCES slice(id) ON DELETE CASCADE;

ALTER TABLE ONLY "classification_item" 
    DROP CONSTRAINT classification_item_transaction_id_fkey;
ALTER TABLE ONLY "classification_item" 
    ADD CONSTRAINT classification_item_transaction_id_fkey 
    FOREIGN KEY (transaction_id) REFERENCES transaction(id) ON DELETE CASCADE;

Now the following query will delete not just the my_slice record from table slice, but also all records from tablestransaction and classification_item referencing it:
```
DELETE FROM slice WHERE name='my_slice';
```

That procedure will work even if the original schema is created by an object-relational mapper like SQLAlchemy. However in such a case, take care to re-apply that "patch" whenever the schema changes or is re-created. Only if that can't be implemented automatically, it might not be a good idea after all …

Abe Miessler · Accepted Answer · 2010-09-14 20:07:00Z

8

It's soemthing that is defined in the table rather than the DELETE Query. Example (look at order_id):

CREATE TABLE order_items (
    product_no integer REFERENCES products ON DELETE RESTRICT,
    order_id integer REFERENCES orders ON DELETE CASCADE,
    quantity integer,
    PRIMARY KEY (product_no, order_id)
);

answered Sep 14, 2010 at 20:07

Abe Miessler

85.6k104 gold badges323 silver badges496 bronze badges

Comments

Oleg Tatarchuk · Accepted Answer · 2017-09-26 16:20:52Z

1

It can be delegated to DBMS by set a constraint property 'On delete' = CASCADE. Please see an example.

answered Sep 26, 2017 at 16:20

Oleg Tatarchuk

1211 silver badge4 bronze badges

Collectives™ on Stack Overflow

How does one write a DELETE CASCADE for postgres?

5 Answers 5

1 Comment

2 Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

1 Comment

2 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related