Natural Key

Source: Wikipedia: Natural Key

Natural key

From Wikipedia, the free encyclopedia
Jump to:navigation, search

In relational model database design, 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 such logical relationship, 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.
[edit] External links

* Key Naturals
* Intelligent Versus Surrogate Keys
* Create Data Disaster: Avoid Unique Indexes – (Mistake 3 of 10)
* Joe Celko: Keys and History
* The c2 wiki's discussion about natural versus surrogate keys

Retrieved from ""
Categories: Data modeling

Unless otherwise stated, the content of this page is licensed under Creative Commons Attribution-ShareAlike 3.0 License