Database Keys

Posted on Wednesday, August 22, 2012

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

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]

[1]  Database Keys, Mike Chapple
       Visited 8/2012
[2]  Unique Key
       Visited 8/2012
[3]  Composite Primary Keys
       Visited 8/2012
[4]  Foreign Key
       Visited 8/2012
[5]  Unique Constraint
       Visited 8/2012
[6]  Difference Between Candidate Keys and Primary Key
       Visited 8/2012

No comments:

Post a Comment