Surrogate Key Best Practice

Surrogate keys (unintelligent keys) are an excellent way to preserve the referential integrity of your database and avoid the need for cascading updates of natural keys if they are changed. However, simply using surrogate key is not always enough.

One of the most common best practices for databases designed with surrogate keys is: Any independent table with a surrogate primary key should have a corresponding natural key with uniqueness enforced. When uniqueness is not enforced duplicate rows are possible and your referential integrity is lost.

For example, consider the table below:

student.jpg

In the student table the primary key is a surrogate key, but uniqueness is enforced stringently by protecting the full name of the student and his birthdate as a unique composite natural key.

Another context for protecting surrogate key referential integrity is by protecting the uniqueness of foreign key combinations where uniqueness would be enforced if the table were dependent. For example consider the tables below:

enrollment1.jpg

Note how the enrollment table has a surrogate primary key, however uniqueness is protected by the unique natural key index on the two foreign keys.

If you use surrogate keys and natural keys together when you design your database, you will avoid the pitfalls of duplicate rows in your data.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: