Relational Database Design - Keys

What are all these Keys?

Lots of folks have trouble when starting out in the wacky world of Relational Databases with understanding keys. There are Primary Keys, Unique Keys, Foreign Keys and Front Door Keys - well maybe not the last one!

Each section has a link to a more detailed explanation.

Primary Key

A Primary Key is used ONLY to uniquely identify a row of data in a database.

There is controversy over this BUT in my world a Primary Key will ALWAYS be a non meaningful, non re-cycled number called 'ID' that is generated by the database. Non meaningful because despite what clients may say, ID numbers etc. will change. Non re-cycled because I want to always know that a Primary Key will only ever relate to ONE record, this will become clearer in the section on Foreign Keys.

Simon's Rule: Every Table will have ONE non recycled, Primary Key named 'ID'.

There is a more comprehensive section on Primary Keys in The Database Design section of this site.

There is a lot of confusion over Rowids and Primary Keys. Rowids are pseudo columns used by the Database to locate the position of a row in the database. Rowids MAY CHANGE! They may be re-used by the system when you delete rows from your database. Never Use a SQLite Rowid as a Primary Key!

Simon's Rule: Never use Rowid as a Primary Key

Unique Key

Unique Keys enforce uniqueness on one or more columns.

A Unique Key is a column or combinations of columns that may be used to enforce and uniquely identify a row in the table. For an example let's imagine a table containing the participants in a school's sports day race - I know this is not a normalised table - it's just an example!

ID (Primary Key) Event Child
1 Three legged July Smith
2 Three legged Fred Smith
3 Egg And Spoon Fred Hill
4 Egg And Spoon Lucy Lockett

To ensure that each Child could only be entered once in a race we could add a Unique Key constraint on a combination of Event and Child columns. If we wanted to only allow a Child to compete in one event then a Unique Key constraint on the Child column would achieve this.

There may be one, many, or no Unique Keys (or indexes) on a Table.

Unique Keys, Unique Constraints, Unique Indexes - all the same thing

Foreign Key

A Foreign Key links a 'child' table to it's 'parent'. It can only hold the value of the Primary Key value of the 'parent' table. Let's normalise the above example a bit:

Events Table

ID Event
1 Three Legged
2 Egg And Spoon

Participants Table

ID Child
1 July Smith
2 Fred Smith
3 Fred Hill
4 Lucy Lockett

Event Participant Table:

ID Event_ID Child_ID
1 1 1
2 1 2
3 2 3
4 2 4

In this example Event_ID is a Foreign Key to the Events table. Child_ID is a Foreign Key to the Participants table.

Foreign Keys join tables to 'parent' tables and take only the Primary Key value of that parent.

There is a more comprehensive section on Foreign Keys in The SQL School section of this site.