Menu
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?
618
A surrogate key is a unique key for an entity in the client's business or for an object in the database. Sometimes natural keys cannot be used to create a unique primary key of the table. This is when the data modeler or architect decides to use surrogate or helping keys for a table in the LDM.
The only good thing in surrogate keys is that you don't have to repeat the actual value in any table referencing your own. Surrogate keys are good when you can't be sure if your natural keys will stay unique, or change; and you can almost never be sure of that.
Disassociation. The values of generated surrogate keys have no relationship to the real-world meaning of the data held in a row. When inspecting a row holding a foreign key reference to another table using a surrogate key, the meaning of the surrogate key's row cannot be discerned from the key itself.
1: A primary key value must be unique A primary key uniquely identifies each record within a table and relates records to additional data stored in other tables. A natural key might require several fields to accomplish a unique identity for each record. A surrogate key is unique in and of itself.
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.
181
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
