|What are Technical (Surrogate, Synthetic, Pseudo, Serial) Keys?|
A technical key (aka surrogate key, synthetic key, pseudo key, serial key) is a key consisting of (usually) one column containing a serial number or other calculated unique identifier assigned by the system.
While Microsoft is promoting its GUID and some modern DBMS products (e.g., Sybase, SQL Server, MS Access) have such serial numbers inherent as data types, many of us have constructed our own for years by maintaining a system table with next number counters.
Advantages of Technical Keys
Commonly Cited Drawbacks of Technical Keys
Opinions on Technical Keys
Many authors and data modelers abhor technical keys, claiming that "natural" keys are more appropriate. Others, like Graeme Simsion, are more moderate and suggest using whatever works. And the OO folks insist that only technical keys (the famous OID) are acceptable.
Having used technical keys for many years along with occasional natural keys, we have recently come to the opinion that the only secure solution is a purely serial - i.e, technical - key.