Home > Software design >  Postgres- hard delete with cascade to relation table
Postgres- hard delete with cascade to relation table

Time:09-14

I am writing sql query (using postgres 14) to DELETE row from tags table:

Tags table:

id team_id name
1 91 Dev Team

But, It should CASCADE to the team_member_tag table too:

id team_member_id tag_id
1 289 1

Joined by team_member table:

id team_id member_id
289 91 1

I tested it, but It seems like my INNER JOIN is out of order. I do not know what I am doing wrong?

My query:

            DELETE tag t, team_member_tag tmt
            FROM tag
            LEFT JOIN team_member_tag tmt ON t.id = tmt.team_member_id
            LEFT JOIN team_member tm ON tm.id = tmt.team_member_id 
            WHERE team_member.member_id = :teamId
            AND team_member.member_id = :teamMemberId

    $stmt->bindValue("teamId", 91 PDO::PARAM_INT);
    $stmt->bindValue("teamMemberId", 289, PDO::PARAM_INT);

I tried first with SELECT, and it says query is OK but returns nothing:

SELECT
    name
FROM tag as t
LEFT JOIN team_member_tag tmt ON t.id = tmt.team_member_id
LEFT JOIN team_member tm ON tm.id = tmt.team_member_id
WHERE name = 'Dev Team'
AND tmt.team_member_id = 289 AND tm.team_id = 91;

CodePudding user response:

For postgres, you need a CTE to do that, if you don't have casacding references see

    SELECT t.*, tmt.*
    FROM tag t
    INNER JOIN team_member_tag tmt ON t.id = tmt.tag_id
    INNER JOIN team_member ON tmt.team_member_id = team_member.id
    INNER JOIN member ON team_member.member_id = member.id
    WHERE team_member.team_id = 91
    AND team_member.member_id = 1;
id team_id name id team_member_id tag_id
1 91 Dev Team 1 289 1
SELECT 1
WITH Bdeletes AS (
    DELETE FROM team_member_tag tmt 
  WHERE tmt.team_member_id IN (SELECT team_member.id
  FROM team_member
  WHERE team_member.team_id = 91
    AND team_member.member_id = 1) 
  returning tmt.tag_id 
)
DELETE from tag t
  where t.id
  IN( SELECT Tag_id FROM Bdeletes)

DELETE 1
SELECT * FROM tag
id team_id name
SELECT 0
SELECT * FROM team_member_tag
id team_member_id tag_id
SELECT 0

fiddle

But if you add a REFERENCE you can do it in one adding more refences would also delete more rows from other tables

CREATE TABLE Tag 
    ("id" int  primary key, "team_id" int, "name" varchar(8))
;
    
INSERT INTO Tag 
    ("id", "team_id", "name")
VALUES
    (1, 91, 'Dev Team')

CREATE TABLE
INSERT 0 1
CREATE TABLE team_member_tag 
    ("id" int, "team_member_id" int, "tag_id" int references Tag (id) on delete cascade )
;
    
INSERT INTO team_member_tag 
    ("id", "team_member_id", "tag_id")
VALUES
    (1, 289, 1)
;

CREATE TABLE
INSERT 0 1
CREATE TABLE team_member 
    ("id" int, "team_id" int, "member_id" int)
;
    
INSERT INTO team_member 
    ("id", "team_id", "member_id")
VALUES
    (289, 91, 1)
;
CREATE TABLE
INSERT 0 1
CREATE TABLe member("id" int)
CREATE TABLE
INSERT INTO member VALUES(1)
INSERT 0 1
    SELECT t.*, tmt.*
    FROM tag t
    INNER JOIN team_member_tag tmt ON t.id = tmt.tag_id
    INNER JOIN team_member ON tmt.team_member_id = team_member.id
    INNER JOIN member ON team_member.member_id = member.id
    WHERE team_member.team_id = 91
    AND team_member.member_id = 1;
id team_id name id team_member_id tag_id
1 91 Dev Team 1 289 1
SELECT 1
    DELETE FROM tag
  WHERE id IN
      (SELECT tag_id FROM  team_member_tag tmt 
  INNER JOIN team_member ON tmt.team_member_id = team_member.id
  WHERE team_member.team_id = 91
    AND team_member.member_id = 1) 

DELETE 1
SELECT * FROM tag
id team_id name
SELECT 0
SELECT * FROM team_member_tag
id team_member_id tag_id
SELECT 0

fiddle

  • Related