database design - When not to use surrogate primary keys?

Question

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?

Answer 1

I would say the following criteria must be met:

• your natural key must be absolutely, positively, no-exceptions-allowed, unique (things like names, social security numbers etc. usually seem to be unique - but really aren't)

• your natural key should be as small as an INT, e.g. not significantly more than 4 bytes in size (don't use a VARCHAR(50) for your PK, and especially not for your clustering key in SQL Server !)

• your natural key ought to be stable, e.g. never change (OK, with ISO country codes, this is almost a given - except when countries like Yugoslavia or the USSR collapse, or other like the two Germanies unite - but that's rare enough)

If those conditions are met, you can consider a natural key as your PK - but that should be the 2% exception in all your tables - not the norm.