Ran into this issue at my client recently and it was hard to convince the db admin why surrogate keys was an advantage for a developer working in an ORM solution
- business requirements changes, then you have to change the natural key
- not easy to index an alphanumeric or String value
- can’t expose the natural key to end users who can then guess the business key and use for querying undesireable data
- you can’t rename the type without changing everything
- If using strings, joins are a bit slower as compared to the int data-type joins, storage is more as well. Since storage is more, less data-values get stored per index page. Also, reading strings is a two step process in some RDBMS: one to get the actual length of the string and second to actually perform the read operation to get the value.
- Locking contentions can arise if using application driven generation mechanism for the key.
- Can’t enter a record until value is known since the value has some meaning.
- A surrogate key is immune to changes in business. In addition, the key depends on only one field, so it’s compact. The auto-incrementing field provides a unique, stable, and compact primary key.
- Business Logic is not in the keys.
- Small 4-byte key (the surrogate key will most likely be an integer and SQL Server for example requires only 4 bytes to store it, if a bigint, then 8 bytes).
- Joins are very fast.
- No locking contentions because of unique constraint (this refers to the waits that get developed when two sessions are trying to insert the same unique business key) as the surrogates get generated by the DB and are cached. Very scalable.
Not to mention, for surrogate keys, a Java developer could assume and code several reusable strategies:
- Have a common BaseIdEntity with an @Id attribute with a common generation strategy or named sequence generators
- Have Base generic services or daos (FooServiceImpl extends BaseServiceImpl
- Generic finder methods with named queries
- And many other referential lookups that can be streamlined once a common id is used
The great primary key debate