> If a PK is on a surrogate key, it is saying that there is no good unique identifying value in the table.
Almost always there is no good unique unchanging identifying value on a table. Take something as simple as "Person" -- there is literally no unique unchangeable value for such an entity. And that's the rule more than the exception.
I take exactly the the opposite approach; nearly all entities should be identified with a surrogate key. You pretty much cannot go wrong with this approach.
In the US state names are pretty stable, but in other countries state/province names have changed so even that value is poor choice for a primary key.
Yes, each set of data should reflect the data you want to use. That's why I said I default to natural keys unless there is good reason to use a surrogate key.
If I have to compare databases I've worked on over the years, the databases that are all surrogate will have many more data errors than a database that is predominantly natural keys. I could write a book on why that happens, but if you don't know relational theory cold (not saying you don't), you should just use natural keys. The short answer is that using all surrogate keys is equivalent to not using unique constraints at all.
Data is bound to change at some point in the future. The entire point of having constraints is to ensure that your data keeps its integrity, now and later on, no matter how the data needs to be updated.
I'm working with a 3rd party database right that used more than a few natural keys and it's awful. The primary key for "users" is username so we can't rename our users to improve our company's username security policy. And that's just one of the problems. We'll be offline for a full 24 hours in April to handle changing the value (for necessary business reasons) of the natural primary key in the client table.
I'm all for constraints, especially uniqueness constraints, where appropriate. You can use unique indexes; you don't have to use natural keys as primary keys. Data is bound to change, so make that change possible.
A badly built database isn't the reason to toss out good practices.
It's actually quite obnoxious to see how bad many databases are, and this often causes a blow-back of tossing the baby out with the bathwater. It's a trap.
I'm not really sure what all the problems are, but if changing username to something else is causing a 24 shutdown, there are many deeper issues than a PK on username.
> A badly built database isn't the reason to toss out good practices.
It's a good reason to toss out bad practices like, for example, using natural keys as primary keys. Which is, almost universally, considered a bad practice. It is, in fact, the bad practice that causes the most pain in this bad database.
> if changing username to something else is causing a 24 shutdown, there are many deeper issues than a PK on username.
This is true; the software is so old it pre-dates most modern RDBMS features and that is a factor in the database design. And it contains a few decades worth of data. And the process to go through the all records and re-do every foreign and primary key value is expensive (through the app server) but is a well-tested path. None of this would be necessary, however, if they had used surrogate keys for these tables. They did use surrogate keys for other tables so it wasn't a technical limitation.
Almost always there is no good unique unchanging identifying value on a table. Take something as simple as "Person" -- there is literally no unique unchangeable value for such an entity. And that's the rule more than the exception.
I take exactly the the opposite approach; nearly all entities should be identified with a surrogate key. You pretty much cannot go wrong with this approach.
In the US state names are pretty stable, but in other countries state/province names have changed so even that value is poor choice for a primary key.