In every database schema there could be many tables.
- In some tables the natural key is a good key.
- In some tables no natural key exists or it is not a good key.
In case 1 you should used the natural key.
In case 2 you should use a autogenerated surrogate key
"Every PK must be natural" is a wrong approach and impossible to achieve. This is an extreme position, but nobody proposes it.
"Every PK must be surrogate" is a wrong approach also but sadly possible to achieve. This is also an extreme position.
Use natural when natural fits and use surrogate when it doesn't.
The "every PK must be surrogate" approach leads to many headaches:
- Migrating data from a database to another is a nightmare since surrogates are sequences that are not syncroniced between databases.
- Data is only meaningful if visualized through "the" application, assuming an "one application -> one database" paradigm.
- Queries are more complex because you have to join every table from the top-most table ( only place where the business key exists, down to the last table ).
- Business people speaks business, they know that a car plaque is ADFG 237, they don't know the car has 155201 ID in the car table. Meetings with the users/business people get awkward because you continue talking about keys they don't know and they continue talking about business keys they know.
- Users will continue to use the natural key for searches, meaning you will have to keep the natural key indexed anyway.
- Interoperation with any external system has the overhead of translating the business key, like the ISO/universaly accepted LHR code for London Heatrow Airport, into the surrogate meaningless key.
Those who defend the "every PK must be surrogate" approach usually argue than character keys are inefficient for joins. Well maybe that was a decade ago, and they fail to produce numbers to support that.
They also argue the update issue when the natural key changes. If the natural key changes frequently, then it's not a good key. If it changes every ten years, that's what cascade update is built for into modern RDBMS.
Also, any hipotethical performance problem of using character keys can be solved by throwing money at it.
But the problems of using the "every PK must be surrogate" approach, cannot be solved by throwing money at them.
Please someone produce any canonical text or bibliography that supports the "every FK must be surrogate" point. On the other hand, the "mixed", "surrogates when necessary" approach is well documented. And Codd's normal forms still exist.
- Use natural keys where a good enough one exists.
- Use surrogates when a good enough natural key doesn't exist.
EDIT: Some people say that "every PK must be surrogate" is an implementation decision, and thus it doesn't violate 3NF and 3NFBC, but they include them first off from the beginning of the design, in the conceptual phase, violating 3NF and 3NFBC, which is an error that is paid by means of writing much much more code than necessary.