Checking whether a record exists before inserting another one does not guarantee uniqueness. Two requests can both see the gap and both attempt the write. If the rule really is “only one of these may exist”, I want the database to enforce it as well as the application explaining it.
PostgreSQL’s constraint documentation covers unique constraints, foreign keys and checks on stored values. Each expresses a different kind of rule. Choose one that matches the actual invariant; a unique constraint on the wrong combination of columns can enforce something quite different from what the product intended.
The application still needs to handle rejection properly. A constraint violation should become a useful result where the operation expects that possibility, rather than an unexplained server error. Existing data also needs checking before introducing a new constraint. An accurate rule can still fail to install because older records do not satisfy it.
I like this division of work: the interface helps people submit a sensible request, and the database protects the stored truth when requests overlap or another writer arrives. It gives the rule a reliable home. The important part is choosing a rule you actually need, rather than adding restrictions simply because the database supports them.
