Natural key
A key made from a value that already means something outside the database, such as an email address, a tax number, or a vehicle identification number.
Natural keys are convenient because people recognize them. Their weakness is that real-world values change, get reused, and get mistyped, and a key that changes breaks every link that pointed to it.
Where it comes up
- Duplicate keys with conflicting valuesTwo rows share an identifier that should be unique, but disagree on other fields.
- The same entity in two systemsOne person or organization exists in two systems under different identifiers, with nothing linking the records.
- Email typos and letter caseEmail addresses differ by capitalization, or contain small typing errors that make them undeliverable.
See also
- Surrogate keyA key generated by a system only to identify a row, with no meaning outside it: a running number, or a random unique ID.
- Primary keyA column, or set of columns, whose value is unique for every row and identifies it: a customer number, an order ID.
- IdentifierA value whose job is to name one particular thing, such as an account number, a part number, or a postal code. It is a label, even when it is made entirely of digits.