Blog

Search | Archive | Categories
There are two ways to go about defining a primary key in relational databases: using an artificial key or a natural key. It is general practice to use an artificial key, but there are pros and cons to both.

Pros to using artificial key:
- Joins are faster since less columns needs to be joined
- Easy to query a single row

Cons to using artificial key:
- Need to add logic to generate an unique artificial key
- No standard way to generate an artificial key

Pros to using natural key:
- No need to generate an artificial key
- Easy to do an insert
- Capture what uniquely identifies a record from a business point of view
- No need to do as much joins since tables carry natural keys

Cons to using natural key:
- Difficult to change the values of natural keys
- All fields in the natural key must be known to do an insert
- Longer query to select a single row if it's a composite key

Definitions:
Key - An attribute utilized to sort and/or identify data in some manner.

Candidate key - A set of one or more columns whose combined values are unique among all occurrences in a given table. Each table may have one or more candidate keys. One of these candidate keys is selected as the table primary key.

Artificial key (surrogate,blind) - A manually generated key value that has no relation to any business meaning (eg. sequences).

Natural key (logical, intelligent) - A key that is determined by the business data (eg. SKU code, SSN)

Primary key - Any candidate key which is chosen as the primary. There is only one single primary key in any table (though it could be a composite primary key).

Secondary/Alternate Key - Candidate keys which are not chosen as the primary key.

Foreign key - A key in a table that is a primary key in another table.

Simple key - A key comprised of a single column.

Composite key - A key comprised of more than one column.

Links:
Relational Keys
Database Glossary - About
Oracle Glossary of Terms
Beginning PHP 4 Databases
Intelligent Versus Surrogate Keys
ComputerDictionaryOnline
Glossary of Database Terminology
Compound vs Composite keys
Database normalization - Wikipedia

Posted: 2003-12-03 16:21:27


<< One Needful ThingThanksgiving Trip >>

RSS/XML