I recently had an interview where I was hit with a lot of questions on different types of keys within a database. I deal with databases a lot but I would not consider myself a DB admin. But, it’s always good to learn some new things, so I decided to dive in and do a little research.

Some of the web sites I visited to gleam information

Primary Key

This is one of the keys I am very familiar with. On a table, within a database, one of the fields is set to be a Primary Key. The idea being that, for the given field (primary key), every value is unique per row. A value can be used to identify a single row. As an example if you were a company listing employee information you might use a company employee number as a primary key. Or for Government purposes a social security number.

As a counter example, it would be a poor idea to use a last name as a primary key as many people share the same last name and only one record can go in per last name, if it’s a primary key.

Many databases do have built in function to auto generate a unique ID per row, think of an incremental number counter as each row goes in.

Composite Primary Key

A composite Primary Key is a Primary Key made up of two or more fields in a table. Rather than just having a single field uniquely identify a row a combination of two or more uniquely identify a row.

One simple example of this would be a table that holds addresses. If you have the fields, Zip, State, city, street name, and street number. These 5 fields combined together could be used as a composite primary key to uniquely identify a row.

Foreign Keys

A Foreign Key is a relationship between two tables. For example, lets assume we have a students table that contains the following fields

student_id
last_name
first_name.

The student_id field is a primary key.

A second table, named tests has the following fields

student_id
test_number
test_score

studend_id and test_number are together a composite primary key.

Now we create a one to many foreign key between

students.student_id -> tests.student_id.

Now any record added to the tests table must have a match from the test.student_id to the students.student_id. If there is not a match the data will not be allowed in the table.

Unique Contraint

A unique constraint is not a key per say, but it’s very similar. A field or a combination of fields in a table can be set to be unique.

If I have a table with a field called last_name and I set it to be UNIQUE. No row in this table can have a duplicate in the field last_name.

Candidate key

I found this great explanation at http://blog.sqlauthority.com/2009/10/22/sql-server-difference-between-candidate-keys-and-primary-key-2/ [6]

And I figure I cannot do any better so I will quote him.

A Candidate Key can be any column or a combination of columns that can qualify as unique key in database. There can be multiple Candidate Keys in one table. Each Candidate Key can qualify as Primary Key. [6]

References

[1] Database Keys, Mike Chapple

http://databases.about.com/od/specificproducts/a/keys.htm

Visited 8/2012

[2] Unique Key

http://en.wikipedia.org/wiki/Primary_key

Visited 8/2012

[3] Composite Primary Keys

http://weblogs.sqlteam.com/jeffs/archive/2007/08/23/composite_primary_keys.aspx

Visited 8/2012

[4] Foreign Key

http://en.wikipedia.org/wiki/Foreign_key