How to Delete a Parent Row in PostgreSQL if No Other Children Reference It?

How to Delete a Parent Row if No Other Children Reference It

When deleting a child row in a database table, it may be necessary to also delete the parent row if no other children reference it. This ensures that the database maintains referential integrity and prevents dangling pointers.

Using a Data-Modifying CTE

PostgreSQL 9.1 and later offer a convenient solution using a data-modifying common table expression (CTE):

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

Key Features:

• The child is deleted unconditionally.
• The parent is deleted only if it no longer has any remaining child rows.
• The last condition is crucial to prevent race conditions and ensure correct results in concurrent operations.

Eliminating Race Conditions

To completely eliminate race conditions, lock the parent row before deleting:

<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 approach ensures that only one transaction can target the same parent at a time, preventing unexpected outcomes.

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