3 ms·

DBAs will emphasize surrogate integer primary keys largely for performance reasons. On SQL Server and others, their performance and size usage in indexes and

by zzzeek 15y ago

DBAs will emphasize surrogate integer primary keys largely for performance reasons. On SQL Server and others, their performance and size usage in indexes and such is well defined and well optimized. I acutally had a few natural PKs suggested in my current schema and the DBAs insisted that every table primary key on a surrogate. Surrogate PKs also allow you to not have to worry about ON UPDATE cascades. A gig I did a few years ago we decided to use some natural PKs, but as is reality, these PKs had to change all the time, not as much as part of regular operation, but more as we modified how the site worked and referenced information as well as when we had to go in and correct data that was mis-labeled (it was a content aggregation site sucking in many gigs of data per day). Plus the natural PKs made our indexes, primary key as well as all the referencing foreign key indexes, huge and poorly performing.

wulczer 15y ago
Yeah, these might all be valid reasons to use surrogate keys. I'm not saying there are none, just that always using one often leads to design mistakes. Incidentally, part III of the keyvil blog posts talks about reasons to use surrogates :) As always, nothing's black or white, sometimes surrogates are OK, sometimes they're not. But I see schemas abusing surrogates much more often than schemas abusing natural keys...
- zzzeek 15y ago
  After my experience in not using them for a large schema, using an int pk is pretty much a default decision for me, unless the table's PK is meaningful compared to another table like an association table. Doing big data migration jobs where the primary keys have to change is very painful. An identity-map ORM like SQLAlchemy is keying everything on primary key - SQLA supports mutable PKs and cascading updates (both via the DB, as well as in memory if you're on sqlite/MyISAM) fully, but the changing in-memory identities is fundamentally painful to deal with when you're shoveling around a lot of data and mutations.

by zzzeek 15y ago

wulczer 15y ago

Yeah, these might all be valid reasons to use surrogate keys. I'm not saying there are none, just that always using one often leads to design mistakes. Incidentally, part III of the keyvil blog posts talks about reasons to use surrogates :) As always, nothing's black or white, sometimes surrogates are OK, sometimes they're not. But I see schemas abusing surrogates much more often than schemas abusing natural keys...

zzzeek 15y ago
After my experience in not using them for a large schema, using an int pk is pretty much a default decision for me, unless the table's PK is meaningful compared to another table like an association table. Doing big data migration jobs where the primary keys have to change is very painful. An identity-map ORM like SQLAlchemy is keying everything on primary key - SQLA supports mutable PKs and cascading updates (both via the DB, as well as in memory if you're on sqlite/MyISAM) fully, but the changing in-memory identities is fundamentally painful to deal with when you're shoveling around a lot of data and mutations.