Database Keys

By Joe Gardiner Thursday, 25th March 2010

Databases use keys to store, sort and retrieve records efficiently and quickly. There are many types of keys: primary keys, candidate keys, and foreign keys, in this guide I'll quickly go over the differences.

Primary Key

A Primary Key is a unique identifier for a record in a database table. It is important as it enables access to a particular record to be very quick, based on a single record using a single unique identifier, and by definition there can only be one instance of the Primary Key for each record.

Commonly used Primary Keys are driver license number, telephone number, or an automatically incrementing user or ID number.

Here are a couple of examples for you...

Good example...

In a library, a good unique identifier for a book would be the books ISBN (International Standard Books Number), as no two books share the ISBN. It would not be appropriate to use the books author as the Primary Key, as many authors share the same name, and one author may have written many books that are available in the Library.

Bad Example...

A UK postcode can be considered a bad Primary Key. It may seem like a good unique at first, however many people may live under the same post code, as a post code represents a small area, not a specific building.

Using Post Code's also limits the scope of your database to just creating records for UK based users, it is extremely unlikely that American users will have a UK post code!

Candidate Key

A Candidate key is the minimum number of attributes (also called fields, or columns) that are required to achieve uniqueness. Unlike the Primary Key, a table can have more than one Candidate Keys, however, the Primary Key will always be selected from one of the Candidate Keys.

For example... A relationship in a Business directory may be between Employee and (Name, Address, Dept). In this example it would be safe to assume the Employee is being uniquely identified by a combination of Name and Address which is therefore a candidate key.

Foreign Key

A foreign key represents the link between one, or a set of columns in one database table, to another column, or set of columns in a separate database table. The columns in the table referencing to another separate table must be the primary or candidate keys in the referenced table.

The values in the referencing table must appear in the referenced row of the second table, so data is linked together, and a child table can be formed of the parent referencing table, forming a subset of data in the referenced table.

A good example would be to imagine an accounts department that has an account database for invoices linking to a supplier database. Supplier details are kept in a separate database table with each supplier being given a 'supplier number' to identify it.

Each invoice in the account table also users the supplier number. The Supplier Number is the primary key in the Supplier Table, and the Foreign Key in the Invoices table is also the Supplier Number and is linked (referencing) to that primary key.

Posted in Guides, MySQL | No Comments »