SQL - delete rows from a table with a constraint-CodePudding

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?

CodePudding user response：

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.