SQL delete elements with a relation table-CodePudding

I have two tables one relationship table. Something like that:

table1 (id PRIMARY KEY, ...)
table2 (id PRIMARY KEY, ...)
rel_table (id_table1 FOREIGN KEY REFERENCES table1(id), id_table2 FOREIGN KEY REFERENCES table2(id))

The tables are not created by me and I cannot modify their definition.

I want to remove the items from table1 that meet a certain condition, and I also want to remove the items from table2 and rel_table that relate them

something similar to the following

begin;

delete t2, rel
from table2 t2
inner join rel_table rel on rel.id_table2 = t2.id
where rel.id_table1 in (...);

delete from table1 where id in (...);

commit;

But I'm using postgresql and this doesn't seem possible. What would be the way to do it?

CodePudding user response：

I am not sure if you can do this:

Lets say this is your table1:

create table table1 (id int primary key)--if it is not primary key, add it

And it's data:

insert into table1 values(1)
insert into table1 values(2)
insert into table1 values(3)

Lets say this is your table2:

create table table2 (id int)

And it's data:

insert into table2 values(1)
insert into table2 values(2)

Lets say this is your rel_table:

create table rel_table (id_1 int, id_2 int)

And it's data:

insert into rel_table values(1, 1)
insert into rel_table values(1, 2)
insert into rel_table values(2, 2)

If you select the data like this:

select * from table1
select * from table2
select * from rel_table

You will see all the data

But then if you add this:

ALTER TABLE table2 ADD FOREIGN KEY (id)
REFERENCES table1(id) ON DELETE CASCADE;

ALTER TABLE rel_table ADD FOREIGN KEY (id_1) 
REFERENCES table1(id) ON DELETE CASCADE;

And then delete one data from table1:

delete from table1
where id = 1

Now when you select the data from all 3 tables you will see the data related to the deleted row from table1 is missing in every table...

Here is a demo

CodePudding user response：

I am not sure if this is something you can use because for both tables table1 and table2 you will need to say what is the id value you need to delete.

WITH 
  a AS 
  ( DELETE FROM table1
    WHERE id = 1
    RETURNING id
  ),
  b AS
  ( DELETE FROM table2 
    WHERE id = 1
    RETURNING id 
  ),
  c AS
  ( DELETE FROM rel_table
    WHERE rel_table.id_1 IN (SELECT table1.id FROM table1)
    or rel_table.id_2 IN (SELECT table2.id FROM table2)
    RETURNING rel_table.id_1, rel_table.id_2
  )
SELECT
    a.id,
    b.id,
    c.id_1, 
    c.id_2
FROM a
  LEFT JOIN b ON a.id = b.id
  LEFT JOIN c ON c.id_1 = a.id and c.id_2 = b.id

Here is a demo