If you want to fully understand what an SQL unique key is, it would be best if you had an idea what primary keys and foreign keys are. In the following tutorial, we will be looking at a few tables, called “Sales”, “Customers”, “Items”, and “Companies”. You can see how the first three are related in the picture below.
However, there is one missing bit – we have to create a connection between the “Sales”, “Customers”, and “Items” fields and the “Companies” table.
The “Companies” Table
You may be wondering why the “Companies” table is so important to us? Let’s take a look at it.
The SQL Primary Key
Well, I hope you can already see that “company_id” is a primary key in it. Spotting this shouldn’t be a whole new ballgame for you. On the other hand, you rarely have two or more companies with the same name, but it’s not impossible, especially if they are registered in a different state or if they operate in different industries.
How about the “headquarters_phone_number” field? You can’t have two US numbers that are completely identical, can you? When you dial a phone number, there is just one phone that will ring somewhere in the US. Therefore, we can conclude that the values in this field have to be unique.
The SQL Unique Key
However, this must mean that “headquarters_phone_number” is also a primary key! Would that be possible? We know that there is one and only one primary key in a table, and here the “company_id” column is a primary key!
Don’t worry, there is a name for this type of field, and it is a unique key. It is used whenever you would like to specify that you don’t want to see duplicate data in a given field, exactly as in our example with the headquarters phone number.
What Are the Differences between a Primary and a Unique Key?
Null Values
There is one substantial difference between unique keys and primary keys, which is that they can contain null values.
Meaning, if we don’t know the headquarters phone number of “Company D”, we can still have this record and our table will be functioning as part of the relational database. However, it won’t give us the phone number for “Company D”.
If instead, we remove number two as “company_id“ and leave the name “Company B” and the relevant phone number, SQL will display an error message. This is because “company_id” is a primary key and we are obliged to provide data in every row of this column.
Number of Keys
In addition, a table can have 0, 1, 2 or more unique keys. This means that depending on the requirements of the database, a table may or may not contain one or more unique key columns. This is different with respect to the characteristics of the primary key constraint:
Important: You can have only one primary key per table.
What Are the Similarities between a Primary and a Unique Key?
Furthermore, what is similar between the two types of constraints is that both can be applied to multiple columns, not just to a single column.
In other words, the unique key constraint can be defined by a single field from the table it is applied to, or it could comprise a few of the columns in that table. Both scenarios are possible.
Using Unique Keys
In conclusion, the work with unique keys has a lot in common with the work with primary keys. Sometimes, we may even need them for the same task. The major difference between the two occurs when it comes to null values. While we can have blank cells in a unique key, we can’t let that happen in a primary key. We can also have more than one unique key in a table.
In the next tutorial, we'll cover the ways tables relate to each other. Check it out.
***
Eager to hone your SQL skills? Check out our SQL course.
Next Tutorial: Relationships Between Tables
