Database Related

In SQL, what are the differences between primary, foreign, and unique keys?

So, what are primary keys, unique keys, and foreign keys?
Databases are great for tracking and organizing information. Information can get ambiguous though, and different tables have to be able to relate information to each other somehow. We solve these dilemmas with primary keys, unique keys, and foreign keys.

I want to tackle primary keys and unique keys first. Many places on the web describe both as being the same thing. That’s mostly true. Both primary keys and unique keys can’t be duplicated and belong to a specific row in a database. There are some very important differences though.

Every table in a database should have primary key but doesn’t necessarily need a unique key. A primary key may or may not relate to the information in a specific row but nonetheless reference that row very specifically. A unique key can function the same way, but it is better to use a unique key to describe a specific property of a row.

Here’s why.
Unique keys can also contain a null value while primary keys cannot. Primary keys cannot contain a null value because the database management system has to be able to find a specific row with the primary key. If the value was null then the database management system wouldn’t be able to find or compare the primary key because database management systems can’t compare NULL. This is important.

Before we go too much further, let’s create an example. So let’s say we have a table called ‘Home Owners’ and another table called ‘House.’

Home Owners
Primary Key Name
1 Billy Bob
2 Mary Jane
3 Jarvis

Houses
Primary Key Address
1 123 Fake St.
2 339 Waverly Way.
3 1067 Wonderful Ave.

Let’s look at the ‘Home Owners’ Tables. The primary key in this case is an incremental integer. It never repeats and can never be reused. People get confused and think the SSN could be a primary key. But notice Jarvis in this case. Jarvis has moved to the United States from Germany. He doesn’t have a SSN. Likewise, Billy’s SSN can be reused after he dies, but that primary key referencing Billy can’t. Likewise, it’s poor practice to expose personal information, like a SSN, as an open identifier.

So how do foreign keys work?

Well a foreign key is nothing more than a primary key from one table used to reference an object in another table. The foreign key allows multiple tables to communicate with each other and show how things relate to each other.

In the ‘Houses’ table we have our primary keys referencing specific houses in the first column. These are specific to the individual houses. The owner column references the primary key from the ‘Home Owners’ table though. We can see that Mary owns two houses while Billy only owns one. We can also track back from 123 Fake St. and see who the owner is and all of their information. The foreign key can be used multiple times in the ‘Houses’ table but it must specifically reference a home owner.

I’ll leave with one final note. Though a unique key can contain NULL, I would not recommend doing so. It’s better to create a standardized value. In the case of Jarvis, NA would work pretty well. Leaving a NULL value, or non-standardized value would make the database not normalized which is not a good thing.

Leave a Reply