We can also have more than one unique key in a table. While we can have blank cells in a unique key, we can’t let that happen in a primary key. The major difference between the two occurs when it comes to null values. Sometimes, we may even need them for the same task. In conclusion, the work with unique keys has a lot in common with the work with primary keys.

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. What Are the Similarities between a Primary and a Unique Key?įurthermore, what is similar between the two types of constraints is that both can be applied to multiple columns, not just to a single column. Important: You can have only one primary key per table. This is different with respect to the characteristics of the primary key constraint: This means that depending on the requirements of the database, a table may or may not contain one or more unique key columns. In addition, a table can have 0, 1, 2 or more unique keys. This is because “ company_id” is a primary key and we are obliged to provide data in every row of this column. 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. However, it won’t give us the phone number for “ Company D”. 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. There is one substantial difference between unique keys and primary keys, which is that they can contain null values. What Are the Differences between a Primary and a Unique Key? Null Values 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. 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!ĭon’t worry, there is a name for this type of field, and it is a unique key.

Therefore, we can conclude that the values in this field have to be 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. 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. Spotting this shouldn’t be a whole new ballgame for you. Well, I hope you can already see that “ company_id” is a primary key in it. You may be wondering why the “ Companies” table is so important to us? Let’s take a look at it. However, there is one missing bit – we have to create a connection between the “ Sales”, “ Customers”, and “ Items” fields and the “ Companies” table.

You can see how the first three are related in the picture below. In the following tutorial, we will be looking at a few tables, called “ Sales”, “ Customers”, “ Items”, and “ Companies”. 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.

Mysql create table with unique key