Unique Key and Null Values

SQL Tutorials 5 min read
unique key
Blog / SQL Tutorials / Unique Key and Null Values

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.

The relations between sales customers and items but not companies

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 primary key for companies is 'company id'

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.

Company names are not always unique

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.

A telephone number will always 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.

data science training

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.

A primary key cannot contain null values but a unique key can

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”.

An example of a record without a phone number

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.

A null value in a primary key will cause an error

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.

You can only have one primary key but multiple unique keys

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.

Both can be assigned to multiple columns

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.

An example of a unique key containing phone number and company columns

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? Learn how to put theory into practice with our hands-on tutorials!

Next Tutorial: Relationships Between Tables

Earn your Data Science Degree

Expert instructions, unmatched support and a verified certificate upon completion!

Leave a Reply

Your email address will not be published.

free SQL cheat sheet

You have Successfully Subscribed!