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
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.
A Foreign Key is a relationship between two tables. For example, lets assume we have a students table that contains the following fields
The student_id field is a primary key.
A second table, named tests has the following fields
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.
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.
I found this great explanation at http://blog.sqlauthority.com/2009/10/22/sql-server-difference-between-candidate-keys-and-primary-key-2/ 
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. 
 Database Keys, Mike Chapple
 Unique Key
 Composite Primary Keys
 Foreign Key
 Unique Constraint
 Difference Between Candidate Keys and Primary Key