How to Delete Parent Records in PostgreSQL When No Children Refer to Them?

Cascading Parent Deletion: Deleting Parent When No Longer Referenced by Children

In database systems, when a child table references a parent table via a foreign key, a typical scenario arises when deleting a child row: should the parent row also be deleted if no other child references it?

PostgreSQL 9.1 and Later: Data-Modifying CTE Approach

For PostgreSQL versions 9.1 or later, a single statement utilizing a data-modifying CTE can efficiently achieve this:

<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>

This statement deletes the specified child row and, if the resulting parent has no remaining child references, deletes the parent as well. The key condition in the EXISTS subquery ensures that the parent is only deleted if all its children have been deleted.

Eliminating Race Conditions

To completely eliminate race conditions, it's crucial to lock the parent row before any deletion occurs:

<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>

This method ensures that only one transaction can lock the same parent at a time, preventing multiple transactions from deleting all children and leaving a dangling parent.

The above is the detailed content of How to Delete Parent Records in PostgreSQL When No Children Refer to Them?. For more information, please follow other related articles on the PHP Chinese website!