Unfortunately, UNIQUE is not enough to prevent the kind of errors I am talking about. They are only easily prevented if the PRIMARY KEY is a natural key.

I have seen many cases where concepts that are UNIQUE NOT NULL for a given business case are ignored as natural keys and surrogate keys are used instead. This results in "duplicated" records (from a business logic perspective) that often end up being manually cleaned, resulting in costs in the orders of 10s of thousands. Not funny.

The idea of a primary key is that you are guaranteed 0 or 1 results, but in the absence of UNIQUE and NOT NULL you may get more than one. That's a powerful abstraction. If your business logic determine that a given attribute or combination of attributes satisfies that condition, that deserves to be the row key.

If you rather use a surrogate key:

a) either you also have to enforce UNIQUE and specially NOT NULL on those attributes (apart from the PK) which is definitely not free lunch (extra indexes, more expensive joins, not benefiting from index-only scans)

b) or often someone forgets either the UNIQUE or NOT NULL on the attribute(s) that have to be constrained as such and data integrity is compromised.

Both cases are bad enough. What's the justification to systematically use surrogate keys then?