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

The one thing that primary, unique, and foreign keys all have in common is the fact that each type of key can consist of more than just one column from a given table. In other words, foreign, primary, and unique keys are not restricted to having just one column from a given table – each type of key can cover multiple columns. So, that is one feature that all the different types of keys share – they can each be comprised of more than just one column, which is something that many people in software are not aware of.

Of course, the database programmer is the one who will actually define which columns are covered by a foreign, primary, or unique key. That is one similarity all those keys share, but there are also some major differences that exist between primary, unique, and foreign keys. We will go over those differences in this article. But first, we want to give a thorough explanation of why foreign keys are necessary in some situations.

What is the point of having a foreign key?

Foreign keys are used to reference unique columns in another table. So, for example, a foreign key can be defined on one table A, and it can reference some unique column(s) in another table B. Why would you want a foreign key? Well, whenever it makes sense to have a relationship between columns in two different tables.

An example of when a foreign key is necessary

Suppose that we have an Employee table and an Employee Salary table. Also assume that every employee has a unique ID. The Employee table could be said to have the ‘master list’ of all Employee ID’s in the company. But, if we want to store employees salaries in another table, then do we want to recreate the entire master list of employee ID’s in the Employee Salary table as well? No – we don’t want to do that because it’s inefficient. It would make a lot more sense to just define a relationship between an Employee ID column in the Employee Salary table and the “master” Employee ID column in the Employee table – one where the Employee Salary table can just reference the employee ID in the Employee table. This way, whenever someone’s employee ID is updated in the Employee table, it will also automatically get updated in the Employee Salary table. Sounds good right? So now, nobody has to manually update the employee ID’s in the Employee Salary table every time the ID is update in the master list inside the Employee table. And, if an employee is removed from the Employee table, he/she will also automatically be removed (by the RDBMS) from the Employee Salary table – of course all of this behavior has to be defined by the database programmer, but hopefully you get the point.

Foreign keys and referential integrity

Foreign keys have a lot to do with the concept of referential integrity. What we discussed in the previous paragraph are some of the principles behind referential integrity. You can and should read a more in depth article on that concept here: Referential integrity explained.

Can a table have multiple unique, foreign, and/or primary keys?

A table can have multiple unique and foreign keys. However, a table can have only one primary key.

Can a unique key have NULL values? Can a primary key have NULL values?

Unique key columns are allowed to hold NULL values. The values in a primary key column, however, can never be NULL.

Can a foreign key reference a non-primary key?

Yes, a foreign key can actually reference a key that is not the primary key of a table. But, a foreign key must reference a unique key.

Can a foreign key contain null values?

Yes, a foreign key can hold NULL values. Because foreign keys can reference unique, non-primary keys – which can hold NULL values – this means that foreign keys can themselves hold NULL values as well.

Some other differences between foreign, primary, and unique keys

While unique and primary keys both enforce uniqueness on the column(s) of one table, foreign keys define a relationship between two tables. A foreign key identifies a column or group of columns in one (referencing) table that refers to a column or group of columns in another (referenced) table – in our example above, the Employee table is the referenced table and the Employee Salary table is the referencing table.

As we stated earlier, both unique and primary keys can be referenced by foreign keys.

Hiring? Job Hunting? Post a JOB or your RESUME on our JOB BOARD >>

Subscribe to our newsletter for more free interview questions.

  • jignesh

    is there any maximum limit in a table to define unique key and foreign key?

  • Ravinder Sudhini

    employee ID updated in the Employee table will not update automatically in the Employee Salary table. This is not possible at least in DB2 and Oracle as there is no “ON UPDATE CASCADE” option.

  • Wardroid

    well. technically they are allowed null, but they still have to be unique…so the statement is still correct

  • Wardroid

    show me a create statement. thats just impossible. NOTE: PK can be composite, but you cant make more than 1 per table.

  • Bhuvan Reddy

    Very Very good site for SQL Developers. Excellent!

  • jess

    Best explanation I have read so far.


  • harjindr

    no other key is called candidate key which can replace primary

  • Purbi

    Yes Goutam, primary key can be a unique key, but unique key doesn’t have to be a primary key.

  • DivRoz

    Nice Explanation. One statement however needs correction : ‘Unique key columns are allowed to hold NULL values’ – This is true for only one NULL Value. You can’t have more than one NULL Value assigned to a Unique key Column. Throws the following error:
    Msg 2627, Level 14, State 1, Line 21

    Violation of UNIQUE KEY constraint ‘UQ__testdata__401849F4792FB0D5’. Cannot insert duplicate key in object ‘dbo.testdata’. The duplicate key value is ().

    The statement has been terminated.

  • Goutam

    Here the creators of these concepts had interchanged the names of unique and primary keys. The primary key is best suited to be called as unique key and vice-versa!

  • Ravish

    Yes we can have 2 PK in a single table, we can create.

  • py

    great explanation

  • py

    it means FK can refer non primary key that is unique key but that doesnt mean it cant refer PK. It can obviously refer to PK.

  • rajendra


  • rajendra

    Can u create one table that can have two primary key.I will appriciate it.

  • vibek

    good one (y)

  • swapnil

    you can have more than 1 primary key in the single table!!!

  • mackj

    Can a unique key be a foreign key on the other table

  • GrtIndian

    it is written “FK can’t refer PK but can refer NON-PK”

  • dudubobo

    What’s the different between the unique key and the super key?

  • dudubobo

    What’s the different between the unique key and the super key? Is the super key be the unique key?

  • rajmohan

    good explanation

  • diksha

    really ……good…….clear……

  • cromateu

    I did not understand this :

    a foreign key must reference a unique key.
    Can you provide an example? or explain in short.

  • Vipul Sachan

    I also have done with some analysis and created my blog –

    Have a look at the below link –

  • pradeep
  • Sachin Tyagi

    Please correct at some location It is mentioned that Foreign can not reference primary key and at some place Foreign key reference primary key.

  • Syed

    Best explanation I have seen so far. Really clears up the concept!

  • Rahul

    Hey nice explanation. All concepts explained clearly.

  • Mahiba


  • sandeep

    Thanks very much

  • varoon10

    Thanks Rahul!

  • varoon10

    Thanks Jack!

  • varoon10

    Thanks Chander!

  • rahul

    nice tutorial

  • jack

    Nice tutorial

  • Chander

    Great work !!! Really good site for clearing the concepts.