 |
17 Aug 2000
Relational
Design Links
and Reading List
©
©
1997-2000
Applied
Information
Science
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
- Unique - assigned by the system assigned, not the user
- Extant - assigned immediately on insert
- Stable - not dependent on business rules, practices, or usage
- Uniform - same data type wherever used, lending itself to generalized reference structures
Commonly Cited Drawbacks of Technical Keys
- Obscure - meaningless even in context and
gibberish out of context
(but is any data model or database meaningful out of context?) - Code-intensive - more columns to write in the join expression
(but do you write SQL or generate it?) - Join-intensive - demands more computing power to execute intermediate joins without embedded grandparent keys (but nothing prevents denormalized replication of join path values for convenience and clarity)
- Bulky - possibly consuming a few more bytes than "natural" keys in some cases (does this matter at pennies per GB?)
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.
More reading: