Jon Phipps’ not-an-NSDL blog

February 9, 2006

Surrogate Primary Keys considered evil?

Filed under: Uncategorized — Jon Phipps @ 8:48 am

Sometimes… Josh Berkus has some enlightening posts about the use of Surrogate Primary Keys in database design:

Primary Keyvil, Part I, Primary Keyvil, Part II, Primary Keyvil, Part III

Which I discovered by following this thread for a while:
http://archives.postgresql.org/pgsql-hackers/2006-01/thrd4.php#00414

Basically a surrogate key, in it’s most common incarnation, is the auto-incremented integer id that uniquely identifies a row in a table and is usually the primary key. It’s a surrogate when another column or combination of columns also uniquely identifies the row and is therefore the actual primary key. For instance a ‘user’ table that requires each user to have a unique login_name and then also assigns a unique user_id. The login_name is the actual primary key, the user_id is a surrogate.

I hadn’t thought about my frequent use of surrogate keys until now, and by extension the general acceptance of their necessity, even their requirement in many frameworks (like Ruby-on-Rails) and object-relational mappings.

Good wake-up call.

Advertisements

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.

%d bloggers like this: