Natural key
From Wikipedia, the free encyclopedia
In relational model database design, a natural key is a candidate key that has a logical relationship to the attributes within that row.
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, unnatural column to your schema.
The main disadvantage of choosing a natural key is that it may need to change if your business requirements change. For example, if you have chosen CustomerNumber as the primary key for a customer, and, subsequently, CustomerNumber becomes alphanumeric instead of numeric, then as well as changing the type of the column, you will need to make changes to all other tables where CustomerNumber is used as a foreign key.
[edit] External links
In a well-designed SQL engine, the values of a natural key appear only once in physical storage and they are referenced when they are used as foreign keys. The structural mechanisms for such referencing vary; Sybase SQL Anywhere uses pointer chains, the SAND engine uses compressed bit vectors, hashing is another way and so forth.
Older SQL engines, which are based on file systems, physically repeat the value in the FOREIGN KEY column and depend on procedural mechanisms to keep them aligned. Treating each table as if it were a separate file instead of implementing the schema as an interrelated whole can make changing the structure of a key very expensive.
Natural keys need three characteristics.
1) Uniqueness. This is the definition of any key. It cannot be NULL because NULLs lack uniqueness.
2) Validation. In the relational model, attributes are scalar values drawn from a domain that has rules. For example, if I see “2006-02-31” as a date data type, I know that it is wrong. Likewise, check digits and regular expressions can guard against false key values.
3) Verification. The key has to identify a real entity and we need a mechism to verify it. For example, if I have a VIN (Vehicle Identification Number), does such a vehicle actually exists? I can go on-line and ask the manufacturer, the Department of Motor Vehicles or an automobile website if this VIN was really used. If I have a (longitude, latitude) pair, I use a GPS or a globe to verify it directly, and so forth.
