Alex Rivera | Logout

When not to use surrogate primary keys?

Asked 2009-11-11T09:49:36.170
13

I have several database tables that just contain a single column and very few rows, often just an ID of something defined in another system. These tables are then referenced with foreign keys from other tables. For example one table contains country codes (SE, DK, US etc). All values are always unique natural keys and they are used as primary keys in other (legacy) systems.

It seems really unnecessary to introduce a new surrogate key to these tables, or?

In general, what are the exceptional cases when surrogate keys shouldn't be used?

database-design primary-key
Edit
Report

1 Answer

1

Natural keys (country codes in your case) are better because

  • they make sense when you see them (Surrogate key alone means nothing to the user. This is important for the DB developers and maintainers who often need to work with raw DB outputs)
  • less joins (often you need only the country code, and they're already in other tables. If you use surrogate keys, then you'll need to join the lookup table)

The downside of the natural keys is that they're tied to the information logic, and if it changes (which sometimes happens), you need to alter a lot of tables, basically overhauling a significant part of the DB.

So, if in your DB the logic doesn't change for many years, use natural keys.

answered 2009-11-11T10:04:43.600

Your Answer