[mpartel]

Application-level checks are prone to race conditions, especially at the default transaction isolation levels of many popular SQL databases.

For example: transaction X deletes a parent row and its child rows while a concurrent transaction Y inserts a new child node. Without a FK, this can leave an orphaned child row.

For similar reasons, SELECT before INSERT is generally not a safe replacement for a UNIQUE constraint.

You don't even need that high a user volume to see these errors in practice.

It's a shame that ORMs don't tend to encourage the use of database-level constraints, but even if I just want to build something quickly in e.g. Rails, I still add the constraints as an extra check because I'd rather get the occasional 500 than an inconsistent database.