> Can you think of any good use case for a non-arbitrary primary key?
Login names; Vehicle registration numbers; flight numbers; domain names; file names; ip addresses. Keys are used in those cases where the enforcement of uniqueness is important for data integrity and identification purposes.
The article mentions auto-incrementing keys, not surrogates. Not the same thing at all. Not all incrementing keys are surrogates and not all surrogates are incrementing keys.
Also, your problem number 1 is a problem whether the natural key in question is the "primary" one or not. Certainly if you choose the wrong natural key then you'll have to fix that - that's why you should take care to make a wise choice of natural key regardless of whether you are also using a surrogate.
> the correct solution is to use a constraint to enforce the uniqueness
That would of course be correct for a natural key. It seems that you too recommend using natural keys and I agree with you on that point. Natural keys are required so that a database can represent the real world accurately and enforce essential business rules. We all rely on websites to enforce the uniqueness of user names; we all expect our bank to enforce the uniqueness of account numbers.
Those who live in an ivory tower where data quality doesn't matter don't like natural keys; those who write applications that have to work in the real world know how foolish it is to leave out natural keys. Those older and wiser heads will have seen and dealt with the serious practical consequences of shoddy databases where natural keys weren't implemented. I think that is the problem being referred to in this thread.
Login names; Vehicle registration numbers; flight numbers; domain names; file names; ip addresses. Keys are used in those cases where the enforcement of uniqueness is important for data integrity and identification purposes.