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.