• 20 hours
  • Medium

Free online content available in this course.

course.header.alt.is_video

course.header.alt.is_certifying

Got it!

Last updated on 11/25/19

Understand primary keys

Log in or subscribe for free to enjoy all this course has to offer!

In the last chapter, I had a class named  Dog. If all the dogs in the building were named  Spot , not only it would be a building with a boring name for all the dogs, but also, I couldn't ask for a specific rows information without adding something to the table.

A similar problem occurs in the table below that lists friends. As you can see, I have a lot of friends named  Fred.

firstName

lastName

hairColor

Fred

Sted

black

Fred

Martina

NULL

Martina

Fred

brown

Fred

Red

blond

Fred

Red

blue

There are two people with exactly the same name and a few other people with the same first names and still others whose last and first names are the same if reversed.

If I want to get my friend, Fred Red's info, I could ask the database to: “Give me Fred Red's row of info.”

Unfortunately, the database won't know if it should give me the row for Fred Red whose hair is blond or the Fred Red whose hair is blue.

To clarify the confusion, I need to add a column to the table that is reserved for unique entries.

So I added the  friendID  column.

friendID

firstName

lastName

hairColor

1

Fred

Sted

 black

2

Fred

Martina

 NULL

3

Martina

Fred

 brown

4

Fred

Red

 blond

5

Fred

Red

 blue

I don’t care what a friend’s ID is, just that the ID is unique, and that I know what it is. :)

It's no longer an issue that all my friends have  Fred in their name (although I may have to ask myself why I am fond of people named Fred).‌

Now, I can just ask the database, “Give me the row where the  friendID  is  4 ” and without question, the database will return Fred Red with blond hair.

Furthermore, in setting up the database, I can tell it that the  friendID  is going to be a unique column. So if I try to put in a new friend and give them an ID that has already been used, the database won't let me.

Since I'm a bit lazy and I don't trust myself, I'll tell the database for every new row, it should come up with the unique ID.

The database will usually take the last unique ID it created and just increment it by 1. In database jargon, this field is getting auto incremented.

The Primary Key (PK)

Typically a field like  friendID  will be anointed as the the table's Primary Key a.k.a. the PK.

The PK is a column of unique fields that won't ever change and will be the primary way that we are going to ask the database for a row. It is very often a field that is auto incremented. In fact, you should always have a table's Primary Key auto increment unless you are sure that it won't ever change. 

To add some nuance, you can have other columns in the table that are specified to be unique that aren't the primary key.

A column of unique values just means that the database will protest if you try to put in the same thing twice.

Email addresses or usernames are often unique but not the primary keys of a table because while they should be unique, they can change.

Giving the PK this priority helps the database search efficiently. Furthermore, the PK is used when we link up our many tables.

The  Friend  class diagram will now look like this:

Now that we a have a primary unique identifier (I mean a PK!) for each row we can use those as keys to lock and unlock the connections between specific rows in different tables. That's what we are going to do in the next chapter!‌

Example of certificate of achievement
Example of certificate of achievement