Natural key
Encyclopedia
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 key
s. 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.
Relational model
The relational model for database management is a database model based on first-order predicate logic, first formulated and proposed in 1969 by Edgar F...
database
Database
A database is an organized collection of data for one or more purposes, usually in digital form. The data are typically organized to model relevant aspects of reality , in a way that supports processes requiring this information...
design, a natural key is a candidate key
Candidate key
In the relational model of databases, a candidate key of a relation is a minimal superkey for that relation; that is, a set of attributes such that# the relation does not have two distinct tuples In the relational model of databases, a candidate key of a relation is a minimal superkey for that...
that has a logical relationship to the attributes within that row
Row (database)
In the context of a relational database, a row—also called a record or tuple—represents a single, implicitly structured data item in a table. In simple terms, a database table can be thought of as consisting of rows and columns or fields...
. A natural key is sometimes called a domain key.
The main advantage of a natural key over a surrogate key
Surrogate key
A surrogate key in a database is a unique identifier for either an entity in the modeled world or an object in the database. The surrogate key is not derived from application data.- Definition :There are at least two definitions of a surrogate:...
, which has no
such logical relationship, is that it already exists; there is no need to
add a new, artificial column
Column (database)
In the context of a relational database table, a column is a set of data values of a particular simple type, one for each row of the table. The columns provide the structure according to which the rows are composed....
to the schema
Logical schema
A Logical Schema is a data model of a specific problem domain expressed in terms of a particular data management technology. Without being specific to a particular database management product, it is in terms of either relational tables and columns, object-oriented classes, or XML tags...
. 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 key
Foreign key
In the context of relational databases, a foreign key is a referential constraint between two tables.A foreign key is a field in a relational table that matches a candidate key of another table...
s. 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
Multivalued function
In mathematics, a multivalued function is a left-total relation; i.e. every input is associated with one or more outputs...
related tables need to be updated also. The secondary disadvantage of choosing a natural key is identifying uniqueness
Unique key
In relational database design, a unique key can uniquely identify each row in a table, and is closely related to the Superkey concept. A unique key comprises a single column or a set of columns. No two distinct rows in a table can have the same value in those columns if NULL values are not used...
. 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.