General discussion


Disadvantage of surrogate key

By burzinmk ·
Suppose a surrogate key (which is also the primary key) is used in one of the entity sets. Later, due to some changes it is found that two other entity sets are specializations of this entity set. Now, it is necessary for the primary key to be present in the two specialized entity sets. Now, to autogenerate this key one has to look up two entity sets (tables), find maximum of the key (surrogate key) from the two tables and then increment it using some logic. I think this is quite a bit of an overhead and a bigger problem as the tables grow in size. I have learnt this just by experience. Can this be called as a disadvantage of using a surrogate key?

This conversation is currently closed to new comments.

Thread display: Collapse - | Expand +

All Comments

Collapse -

Disadvantage of surrogate key

by matthew_macfarland In reply to Disadvantage of surrogate ...

It may not be necessary for you to look up max values in the tables. You can create a key generator table with column to store the last value that was used as a key and then increment it each time you assign a key to a new record(s). If you have many tables that need this then make a column for the table name and one for the key value and a single table can be used to store keys for many tables. If you are using the Oracle database then just use sequences.

Collapse -

Use identifier

by rwblaas In reply to Disadvantage of surrogate ...

If your using SQL Server you can use the identifier data type. Be carefull though with these surrogate keys ... always make sure you have a logical key defined. You could run into some very nasty integrity problems if things go wrong when you only use surogate keys. My advice is to only use surogate keys only if your logical primary key is longer then 4 to 5 fields. In this case, from a technical point of view it could be interesting to use a technical key. Otherwise .... it's not worth the risks of a corupt database.

Related Discussions

Related Forums