Using Surrogate Key in Databases

I prefer using surrogate key (a non business key used as an identifier e.g. Instead of using a StudentID as an identifier, we may use a separate RowID column to identify each student record) whenever I can. Different people have different opinions about using surrogate keys in databases. Some will always use a surrogate key while others won’t. It all comes down to the personal preference of a database developer.

However, based on my experience working with surrogate keys, I am off the opinion that surrogate keys are both good and bad. Don’t get me wrong when I call them bad. They don’t do any harm rather add to the working load on behalf of the developer. Following are some of the pros and cons of using a surrogate key:


PROS

1. Primary keys (or natural keys) are hard to change. In programming, there are times when we may need to change the primary key e.g. we can change a Student-ID from type char (6) to char (8) to accommodate more students. If Student-ID is defined as a primary key, it must have links in several tables. This means, the change must be reflected in all the associated tables (a nightmare for a developer). However, if a separate surrogate key is defined, Student-ID type can be changed without affecting multiple tables.

2. A surrogate key can assist developers in programming e.g. suppose a table has a surrogate key defined while the unique key is defined by the combination of three columns. If the data in this table is displayed in a gridview in asp.net, the developer can easily write code to Update and Delete records based on the primary key. In this case, he doesn’t have to worry about all three keys which define the unique key.

3. There is no locking contention since a surrogate key is generated by the database and cached making them highly scalable.


CONS

1. Data integrity becomes the responsibility of the developer. Whenever a record is inserted or updated, the developer must check if a similar natural key already exists to avoid duplication. If the natural key was defined as the primary key, the database would take care of data integrity.

2. Excessive joins are needed since joins are dependent on keys with business value and not a surrogate key e.g. to get details of a student from different tables, a developer would prefer to use his Registration-ID and not a surrogate key.

3. An separate index must be defined on the natural key.

4. From a programming point of view, surrogate keys cannot be used as a search key.

I hope you find this blog as useful. Stay tuned for more...

I start writing again - finally!

I have been away from blogging for quite some time. This can be attributed to my busy schedule (and some what laziness :-). However, I now plan to write on regular basis. I have some interesting topics in mind to start with. I also plan to finish my incomplete 'LINQ Explained' series. Honestly, LINQ is one topic which requires thorough and deep understanding of concepts before talking about it. In coming days, I also plan to blog about SharePoint Server, Enterprise Concepts and ASP.NET. So stay tuned for more...