SQL - delete rows from a table with a constraint

Question

I am using psql (PostgreSQL) 12.3.

I have 3 tables, one being the join table.

+-----------------------------+
| supplierbookingconfirmation |
+-----------------------------+
|          uri                |
+-----------------------------+

+-----------------------------------------------------------+
|      approvalsubmission_supplierbookingconfirmation       |
+-----------------------------------------------------------+
| approvalsubmission_id | automaticallybookedservices_uri   |
+-----------------------------------------------------------+

+-----------------------+
|  approvalsubmission   | 
+-----------------------+
|  id | conclusiondate  |
+-----------------------+

I would like to clear legacy data older than 5 years old in all 3 tables. My problem is only one of the tables (approvalsubmission) has the date to determine the age, so I need to join the tables when I do the delete.

I have the following SQL:

DELETE from supplierbookingconfirmation sc where sc.uri IN (
SELECT distinct(c.uri) from supplierbookingconfirmation c 
inner join approvalsubmission_supplierbookingconfirmation s ON c.uri = s.automaticallybookedservices_uri
inner join approvalsubmission a ON a.id = s.approvalsubmission_id 
where a.conclusiondate < now() - interval '5 year');

DELETE from approvalsubmission_supplierbookingconfirmation c where c.approvalsubmission_id IN (
SELECT distinct(s.approvalsubmission_id) from approvalsubmission_supplierbookingconfirmation s 
inner join approvalsubmission a ON a.id = s.approvalsubmission_id 
where a.conclusiondate < now() - interval '5 year');

DELETE from approvalsubmission a where a.conclusiondate < now() - interval '5 year';

However, when I try delete from the from the first table (supplierbookingconfirmation), I get the following error:

ERROR: update or delete on table "supplierbookingconfirmation" violates foreign key constraint "fk8de3b77230a9f24d" on table "approvalsubmission_supplierbookingconfirmation" DETAIL: Key (uri)=(31fb11ff-2acd-4776-b211-e2bef5daac2d) is still referenced from table "approvalsubmission_supplierbookingconfirmation". SQL state: 23503

If I try delete from the join table (approvalsubmission_supplierbookingconfirmation) first, then the delete from the supplierbookingconfirmation table won't work because the other query needs to use the join table.

Question

What is my best approach? Do I need store the results, and then delete the join table followed by the supplierbookingconfirmation table?

Laurenz Albe · Accepted Answer · 2021-10-06 15:13:39Z

2

You could use CTEs:

WITH rem AS (
   DELETE FROM approvalsubmission_supplierbookingconfirmation
   WHERE ...
   RETURNING approvalsubmission_id, automaticallybookedservices_uri
), del1 AS (
   DELETE FROM supplierbookingconfirmation AS s
   USING rem
   WHERE s.uri = rem.automaticallybookedservices_uri
)
DELETE FROM approvalsubmission AS a
USING rem
WHERE a.id = rem.approvalsubmission_id;

That way you can delete from all tables in a single statement, and the delete from approvalsubmission_supplierbookingconfirmation happens first.

answered Oct 6, 2021 at 15:13

Laurenz Albe

257k22 gold badges313 silver badges389 bronze badges

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

Collectives™ on Stack Overflow

SQL - delete rows from a table with a constraint

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related