How to Implement Cascading Parent Deletion with Foreign Key Constraints in PostgreSQL?

Cascading Parent Deletion with Foreign Key Constraint

In relational database systems, deleting a child row often necessitates the deletion of the parent if it is no longer referenced by any other child. This task can be accomplished in PostgreSQL versions 9.1 and later using a data-modifying CTE (Common Table Expression).

Data-Modifying CTE Approach

<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 CTE first deletes the specified child row. It then returns the parent ID and the deleted child ID. The second DELETE statement uses this information to delete the parent row if it has no other remaining children. The condition c.child_id <> x.child_id ensures that only children other than the one being deleted are considered.

Eliminating Race Conditions

To prevent potential race conditions where concurrent transactions may lead to unexpected results, the parent row can be locked before the deletion process. This is achieved by using the FOR NO KEY UPDATE clause in the CTE to lock the parent row.

<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 limits the execution of the risky DELETE operations to a single transaction, effectively eliminating the possibility of concurrent interference.

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