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