Discussion Locked

Disadvantage of surrogate key

0 helpful
2 replies
Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.
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?

2 replies

by matthew_macfarland

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.

by rwblaas

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.

This discussion is closed to new replies.