Postgres- hard delete with cascade to relation table-CodePudding

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