CODEWITHSUNDEEP
sqlpostgresqlforeign-keysconstraints
User1291
User1291

Reputation: 8182

SQL - How do I delete two entities referencing each other

So I have two entities referencing each other, parent, child.

child must be deleted if parent is deleted, but cannot be deleted while there's still a parent referencing it.

These are the two constraints I've been given:

ALTER TABLE public.parent
  ADD CONSTRAINT parent__child_id__fk
    FOREIGN KEY (child_id) REFERENCES child(id)
    ON DELETE CASCADE
;

ALTER TABLE public.child
  ADD CONSTRAINT child__parent_code__id__fk
    FOREIGN KEY (parent_code, id) REFERENCES parent(code, child_id)
      ON UPDATE CASCADE
      ON DELETE RESTRICT
    DEFERRABLE INITIALLY DEFERRED
;

I now want to delete a parent (and the corresponding child) ...

SQL Error [23503]:
ERROR: update or delete on table "parent" violates foreign key constraint
"child__parent_code__id__fk" on table "child"

    Detail: Key (code, child_id)=(A0B7EBF6-3_DELETE_ME, 10)
            is still referenced from table "child".

Whoop-dee-doo ...

Yes, it's referenced by the bloody entry I'm trying to delete...

(which I know because there's a unique constraint on parent.code)

Looks like I CAN delete the entry if I set the child's fk to ON DELETE CASCADE, but that doesn't seem to be what the guy breathing down my neck wants, which is "if you delete a parent delete its child, too, if you delete a child that has a parent, DON'T".

How do I achieve this?

Upvotes: 3

Views: 644

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 246318

Delete from both tables in one statement using a CTE:

WITH x AS (
    DELETE FROM parent
    WHERE ...
    RETURNING id
)
DELETE FROM child
WHERE ...;

Upvotes: 2

Related Questions