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

FOLLOW Varoon Sahgal, Author of ProgrammerInterviewon

32 Responses to “Differences between Primary and Foreign Keys”

  1. jess says:

    Best explanation I have read so far.

    Thanks!

  2. harjindr says:

    no other key is called candidate key which can replace primary

  3. Purbi says:

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

  4. DivRoz says:

    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.

  5. Goutam says:

    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!

  6. Ravish says:

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

  7. py says:

    great explanation

  8. py says:

    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.

  9. rajendra says:

    HI

  10. rajendra says:

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

  11. vibek says:

    good one (y)

  12. swapnil says:

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

  13. mackj says:

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

  14. GrtIndian says:

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

  15. dudubobo says:

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

  16. dudubobo says:

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

  17. rajmohan says:

    good explanation

  18. diksha says:

    really ……good…….clear……

  19. cromateu says:

    I did not understand this :

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

  20. Vipul Sachan says:

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

    Have a look at the below link –

    http://vsstack.blogspot.in/p/keys-in-sql-server.html

  21. Sachin Tyagi says:

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

  22. Syed says:

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

  23. Rahul says:

    Hey nice explanation. All concepts explained clearly.

  24. Mahiba says:

    Thanks

  25. sandeep says:

    Thanks very much

  26. varoon10 says:

    Thanks Rahul!

  27. varoon10 says:

    Thanks Jack!

  28. varoon10 says:

    Thanks Chander!

  29. rahul says:

    nice tutorial

  30. jack says:

    Nice tutorial

  31. Chander says:

    Hi,
    Great work !!! Really good site for clearing the concepts.
    Thanks

Leave a Reply

*