In relational model database design, a natural key is a key that is formed of attributes that already exist in the real world. For example, a USA citizen's social security number could be used as a natural key. In other words, a natural key is a candidate key that has a logical relationship to the attributes within that row. A natural key is sometimes called a domain key.
The main advantage of a natural key over a surrogate key, which has no meaning outside the database environment, is that it already exists; there is no need to add a new, artificial column to the schema. Using a natural key (when one can be identified) also simplifies data quality: It ensures that there can only be one row for a key; this "one version of the truth" can be verified, because the natural key is based on a real-world observation.
The main disadvantage of choosing a natural key is that its value may change and the relational database engine may not be able to propagate that change across the related foreign keys. For example, if person_name is used as the primary key for the person table, and a person gets married and changes name, then all of the one-to-many related tables need to be updated also. The secondary disadvantage of choosing a natural key is identifying uniqueness. The primary key must consist of the attributes that uniquely identify a row. However, it may be difficult (or it may add constraints) to create a natural key on a table. For example, if person_name is used as a primary key for the person table, many persons may share the same name and all but the first entry will be rejected as a duplication. The uniqueness constraint may be overcome by adding an additional column to the primary key, like street_address, to increase the likelihood of uniqueness.
Famous quotes containing the words natural and/or key:
“The superstition respecting power and office is going to the ground. The stream of human affairs flows its own way, and is very little affected by the activity of legislators. What great masses of men wish done, will be done; and they do not wish it for a freak, but because it is their state and natural end.”
—Ralph Waldo Emerson (1803–1882)
“They have thrown away her electric toothbrush, someone else slips
The key into the lock of her safety-deposit box
At the Crocker-Anglo Bank; her seat at the cricket matches
Is warmed by buttocks less delectable than hers.”
—Randall Jarrell (1914–1965)