It seems like your intention is to delete the parent record when the last child is deleted.
If so, while you can issue an unconditional
DELETE to remove the parent and let foreign key relationships with existing children prevent it, this may not be the best strategy. To make it work at all you must do the
DELETE of the parent in a different transaction (or sub-transaction) to the
DELETE of the child, otherwise the whole transaction's work will be undone when the
DELETE of the parent fails and the transaction aborts.
Doing it in an entirely separate transaction is not very safe as it'll leave a window where there's a parent with no children - and if you ever re-use keys it's totally unacceptable.
Deleting the child then deleting the parent in a subtransaction using
ROLLBACK TO SAVEPOINT is OK, albeit awkward. You need to think about what happens when two concurrent transactions delete the last two children of the same parent, though; you'd land up with an orphan parent record because each would see the "remaining" child the other one is in the process of deleting. To prevent that you'll need to
SELECT ... FOR UPDATE the parent before deleting the child.
SELECT 1 FROM parent WHERE parent_id = 1 FOR UPDATE;
DELETE FROM child WHERE child_id = 11 and parent_id = 1;
DELETE FROM parent WHERE parent_id = 1;
-- In the application see if the DELETE was successful. If it was, COMMIT.
-- if it failed, run:
ROLLBACK TO SAVEPOINT delete_parent;
-- before committing the deletion of the child.
Alternately, because you're forcing transactions that remove children from a parent to occur serially, you can just replace the
SAVEPOINT and everything below with:
DELETE FROM parent
WHERE parent_id = 1 AND NOT EXISTS (
SELECT 1 FROM child c WHERE c.parent_id = parent.parent_id
An alternative strategy is to use a
SERIALIZABLE isolation transaction to optimistically delete the parent instead of using the initial
SELECT ... FOR UPDATE. If somebody else creates a new child or does something else that'll conflict, serializable isolation in PostgreSQL 9.1 or above will abort one of the conflicting transactions. Your application must be prepared to deal with errors and re-run aborted transactions.