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.
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.