How to Implement Cascading Parent Deletion Upon Child Removal in PostgreSQL?

Cascading Parent Deletion upon Child Removal

In scenarios where a parent table has a column referencing child rows, it becomes necessary to delete the parent record if no child rows remain. PostgreSQL provides various methods to achieve this.

One approach is through a data-modifying CTE (Common Table Expression) in PostgreSQL 9.1 or later:

<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   -- (1)
   );</code>

Through this CTE, the child is always deleted, while the parent is only deleted if it has no other children. Note, condition (1) is crucial to avoid unexpected results from concurrent operations.

However, this approach is not immune to race conditions. To eliminate them completely, a parent row can be locked before deletion:

<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              -- (2)
   FOR    NO KEY UPDATE                -- (3)
   )
 , 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   -- (4)
   );</code>

In this case, only one transaction can lock the same parent, preventing concurrent deletion scenarios. (2) is the child row to be deleted. While (3) locks the parent row, (4) verifies no other child exists before deleting the parent.

For clarity, conditions (1) and (4) prevent "false" deletions by ensuring that the parent is only removed if it has no remaining children.

The above is the detailed content of How to Implement Cascading Parent Deletion Upon Child Removal in PostgreSQL?. For more information, please follow other related articles on the PHP Chinese website!