How to Ensure Parent Deletion in PostgreSQL When No Children Reference It?

Ensuring Parent Deletion When Not Referenced by Any Child

When deleting a child row, it is often necessary to delete the parent as well if it is no longer referenced by any other child. This can be achieved in PostgreSQL using a data-modifying CTE, minimizing the risk of race conditions.

Consider the following PostgreSQL statement:

<code class="sql">WITH del_child AS (
    DELETE FROM child
    WHERE  child_id = 1
    RETURNING parent_id, child_id
    )
DELETE FROM parent p
USING  del_child x
WHERE  p.parent_id = x.parent_id
AND    NOT EXISTS (
   SELECT FROM child c
   WHERE  c.parent_id = x.parent_id
   AND    c.child_id <> x.child_id   -- !
   );</code>

Here's how it works:

• The del_child CTE deletes the child row with child_id = 1.
• The DELETE statement attempts to delete the parent row based on the returned parent_id from del_child. However, it only deletes the parent if there are no other children referencing it. This is achieved by checking the existence of any other child with a different child_id (NOT EXISTS subquery).
• The condition c.child_id <> x.child_id ensures that the subquery excludes the child row that was just deleted.

Eliminating Race Conditions

To completely eliminate the possibility of race conditions, it is recommended to lock the parent row before deleting both the child and the parent. This ensures that only one transaction can process this operation at a time, preventing multiple transactions from deleting children and leaving the parent orphaned.

Here's an updated statement with row locking:

<code class="sql">WITH lock_parent AS (
   SELECT p.parent_id, c.child_id
   FROM   child  c
   JOIN   parent p ON p.parent_id = c.parent_id
   WHERE  c.child_id = 12              -- provide child_id here once
   FOR    NO KEY UPDATE                -- locks parent row.
   )
 , del_child AS (
   DELETE FROM child c
   USING  lock_parent l
   WHERE  c.child_id = l.child_id
   )
DELETE FROM parent p
USING  lock_parent l
WHERE  p.parent_id = l.parent_id
AND    NOT EXISTS (
   SELECT FROM child c
   WHERE  c.parent_id = l.parent_id
   AND    c.child_id <> l.child_id   -- !
   );</code>

The above is the detailed content of How to Ensure Parent Deletion in PostgreSQL When No Children Reference It?. For more information, please follow other related articles on the PHP Chinese website!