Natural key can be defined as an identifier of row which is composed of data. The data present in the database will describe according to its attributes. The row identifier in a database is the focal point of clustered and nonclustered indexes.
Why it is called as Natural key?
Consider we have table named students which contains fields like name and roll no. If a name and roll no present in the table are grouped together using a key that particular key is called as natural key. The key is called as natural key because fields like name and roll no are common among all students.
Natural vs Surrogate keys
The use of natural keys is often compared with the use of surrogate keys. But surrogate keys are not substitute for natural keys because they are unnatural. Surrogate keys are used when there is a need to relate two tables which has no logical relation between them. The data stored in columns which use surrogate key don’t have any meaning. In most databases, surrogate keys are used to act as primary key only. Most developers still debate over the superiority between surrogate and natural keys.
How to Choose between Surrogate and Natural Keys?
The RDBMS theory recommends that all the normalized table should contain a primary key. The type of primary key used whether it is natural or surrogate is still debated among all the developers. Very rarely you can see a developer using both surrogate and natural keys. The developers who use both the keys will use the data available and application to reach a decision. This article will guide through easy steps to choose between surrogate and natural keys.
In some databases forcing a natural key has high priority because applications which share tables may create new records. If a natural key is not in these types of applications, it may result in generating same values from different databases. If a combined source code is created to connect the databases, it can accommodate surrogate key. However the process is complicated and the performance of database will reduced drastically.
While working on some applications system may inadvertently throw error which might corrupt the database if surrogate keys are used. It is better to use natural keys while working in these types of applications. If you still insist in using surrogate keys, it is better to backup all the data regularly.
Unnecessary fields added by surrogate key
When a surrogate key is generated an extra field is created in the database. The extra field generated by surrogate key contains the data required to identify the key and relate it to database. The field created by surrogate key requires no maintenance but consumes considerable amount of space. If you are not worried about of space consumed, you can go for surrogate key else fall back to natural key.
Primary key value
Many developers, who are using natural keys, argue that it is necessary for the users to know about primary key value. When surrogate key is used it duplicates the primary key value which prevents the users from witnessing an unique primary key value. Providing index to appropriate fields can prevent duplication of primary key value.
Creating record without primary key value
The value of primary key is must to create a record. If a developer is not aware of the value of primary key, he/she can sought the help of surrogate key. If a natural key is used, it is impossible to create a record without knowing the value of primary key. It is better to opt for surrogate whenever the value of primary key is not available.
Primary key must be compact
If the number of field to identify a record is increased, it will complicate the search. If a natural key is used as a primary key, it will deal with several fields so a separate field will be required to narrow down the search. In surrogate key, it creates a key for every single field which makes searching through the table a easy process.
Primary key must have a unique value
To uniquely identify every record present in a table, a unique value is required by the primary key. The natural primary key may require several fields to identify a record. The surrogate primary key requires is unique so it is easy to identify a record.