Surrogate Primary Keys
There are some instances when a primary key doesn’t exist in the real world or when the existing natural key might not be a suitable primary key. In these cases, it is standard practice to create a surrogate key. A surrogate key is a primary key created by the database designer to simplify the identification of entity instances. The surrogate key has no meaning in the user’s environment—it exists only to distinguish one entity instance from another. One practical advantage of a surrogate key is that since it has no intrinsic meaning, values for it can be generated by the DBMS to ensure that unique values are always provided.
For example, consider the case of a park recreation facility that rents rooms for small parties. The manager of the facility keeps track of all events, using a folder with the format shown in the following Table.
What primary key would you suggest? In this case, there is no simple natural key that could be used as a primary key in the model. Based on the primary key concepts you learned about in previous chapters, you might suggest one of these options:
(DATE, TIME_START, ROOM) or (DATE, TIME_END, ROOM)
You now have a lengthy four-attribute composite primary key. At this point, you can see that the composite primary key could make the implementation of the database and program coding unnecessarily complex.
Surrogate primary keys are accepted practice in today’s complex data environments. They are especially helpful when there is no natural key, when the selected candidate key has embedded semantic contents, or when the selected candidate key is too long or cumbersome. However, there is a trade-off: if you use a surrogate key, you must ensure that the candidate key of the entity in question performs properly through the use of “unique index” and “not null” constraints.
You May Also Like:
Difference between partial completeness and total completeness
Primary Key and Its Key Characteristics
