Postgres 14

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE parent (
  parent_id  integer PRIMARY KEY
, txt text
);

CREATE TABLE child (
  child_id  integer PRIMARY KEY
, parent_id  integer REFERENCES parent(parent_id) ON DELETE RESTRICT -- make sure even this works
, txt text
);

INSERT INTO parent VALUES
  (1, 'foo')
, (2, 'bar')
;

INSERT INTO child VALUES
  (1, 1, 'foo')
, (2, 2, 'bar')
, (3, 2, 'baz')  -- parent_id 2 has two children
;

CREATE TABLE



CREATE TABLE



INSERT 0 2



INSERT 0 3


-- Delete child 1, cascades
WITH x AS (
    DELETE FROM child
    WHERE  child_id = 1
    RETURNING parent_id, child_id
    )
DELETE FROM parent p
USING  x
WHERE  p.parent_id = x.parent_id
AND    NOT EXISTS (
   SELECT 1
   FROM   child c
   WHERE  c.parent_id = x.parent_id
   AND    c.child_id <> x.child_id
   );

DELETE 1


-- Delete child 2, doesn't cascade
WITH x AS (
    DELETE FROM child
    WHERE  child_id = 2
    RETURNING parent_id, child_id
    )
DELETE FROM parent p
USING  x
WHERE  p.parent_id = x.parent_id
AND    NOT EXISTS (
   SELECT 1
   FROM   child c
   WHERE  c.parent_id = x.parent_id
   AND    c.child_id <> x.child_id
   );

DELETE 0


SELECT * FROM parent;
SELECT * FROM child;


parent_id
txt


2
bar



SELECT 1




child_id
parent_id
txt


3
2
baz



SELECT 1