This article will teach you the difference between a primary key and foreign key. This article will also teach you why both of these keys are important when it comes to the maintenance of a relational database structure.

All the examples for this lesson are based on Microsoft SQL Server Management Studio and the AdventureWorks database.  Use my Guide Getting Started Using SQL Server to learn SQL Server today!

What is the difference between a Primary Key and a Foreign Key?

Before we can dig into the difference, let’s first explore primary and foreign key characteristics. Let’s start out by learning about primary keys.

Primary Keys

In order for a table to qualify as a relational table, it must have a primary key.

The primary key consists of one or more columns whose data contained within are used to uniquely identify each row in the table.  You can think of them as an address.  If the rows in a table were mailboxes, then the primary key would be the listing of street addresses.

In order to be a primary key, several conditions must hold true:

  • The columns defining the primary key are unique.
  • Each column may contain duplicate values; however, the combination of column values is unique.
  • No value within the primary key columns is NULL. I would also extend this to include “blank” values.
  • When defining a table you specify the primary key. A table has just one primary key, and its definition is mandatory.
  • Primary keys are stored within an index
  • The index maintains the primary key’s uniqueness requirement. It also makes it easy for foreign key values to refer back to corresponding primary key values, as we will learn about in the following section.

Looking to learn more about database design? One of the first hurdles you’ll want to overcome is Database Normalization. Get our guide to help you with Database Design and Normalization. We take a super complex topic and make it easy for you to understand!

Foreign Keys

A foreign key is a set of one or more columns in a table that refers to the primary key in another table.  There aren’t any special code, configurations, or table definitions you need to place to officially “designate” a foreign key.

In the diagram below look at the SalesOrderHeader table.  The column SalesOrderHeader.CurrencyRateID is a foreign key since it is related to the CurrencyRate.CurrencyRateID. This column CurrencyRate.CurrencyRateID is the primary key of the CurrencyRate table.

Currency Rate ID is the Primary Key and in CurrencyRate and the Foreign Key in SalesOrderHeader
Example of Foreign Key

Keys workingTogether

Look at the following diagram.  Which column is the foreign key?

Hint: BusinessEntityID is the Primary Key in Person
Foreign Key Value That is Part of PK

If you said it was PersonPhone.BusinessEntityID then you are correct. The reason it is a foreign key is that it is referring to a primary key, Person.BusinessEntityID, on the other table.

Coincidentally, PersonPhone.BusinessEntityID is not only a foreign key but is also part of PersonPhone’s primary key.  The PersonPhone table’s primary key is the combination of BusinessEntityID, PhoneNumber, and PhoneNumberTypeID.

Here are foreign key ideas to keep in mind:

  • Unlike primary keys, foreign keys can contain duplicate values.  Also, it is OK for them to contain NULL values.
  • Though not automatically created for foreign keys, it is a good idea to define them.
  • You can define several foreign key within a table.

Wondering about database indexes? You can learn more about how indexes work here!

In the PersonPhone table can you find the other foreign key (see the answer at end of the article)?

Use Object Explorer to Find Database Keys

When you use SSMS you’ll find all sorts of helpful information in the object explorer.  You don’t have to dig deep to find the primary keys.  When you show a table’s columns, but clicking on the Columns folder, the primary key columns have gold keys next to them.

Finding Keys in Object Explorer
Finding Primary and Foreign Keys in Object Explorer

Also, if any of the foreign keys are defined in foreign key constraints, which we’ll learn in the following section, then those columns have FK after them.  These are circled in green in the above diagram.

Foreign Key Constraints

Some database management systems, such as SQL Server allow you to set up foreign key constraints.  These help to enforce referential integrity.  In their simplest form, a foreign key constraint stops you from entering values that aren’t found in the related table’s primary key.

Using the first diagram as our example, you can’t enter the SalesOrderHeader.CurrencyRateID if it doesn’t already exist in the CurrencyRate table.

These constraints come into effect in several ways:

  1. They bar you from changing the foreign key value to one which doesn’t exist as a value in the related table’s primary key.
  2. They stop you from deleting a row from the primary key table. This stops you from creating orphan records.  Orphan records are described as “child records with no parents.”
  3. They stop you from adding a foreign key value that doesn’t exist in the primary key.

In summary, the constraints enforce the relationship between the primary and foreign key tables.

Comparison of Primary Keys to Foreign Keys

To summarize here is a comparison of Primary to Foreign Keys

Comparison of Foreign and Primary Keys.

Answer to Question: Earlier we asked for you to identify the other foreign key in the PersonPhone table.  The correct answer is PhoneNumberTypeID.

Looking to learn more about database design? One of the first hurdles you’ll want to overcome is Database Normalization. Get our guide to help you with Database Design and Normalization. We take a super complex topic and make it easy for you to understand!

Conclusion

A primary key is required to define a relational database table. A foreign key, is one or more columns for another table that references a table’s primary key. In some DBMS’s define foreign key constraints to protect this relationship.

About the author 

Kris Wenzel

Kris Wenzel has been working with databases over the past 30 years as a developer, analyst, and DBA. He has a BSE in Computer Engineering from the University of Michigan and a MBA from the University of Notre Dame. Kris has written hundreds of blog articles and many online courses. He loves helping others learn SQL.

  • I think your sentence: A table is allowed to contain more than one primary key. In the PersonPhone table can you find the other foreign key?

    Should be:A table is allowed to contain more than one FOREIGN key. In the PersonPhone table can you find the other foreign key?

  • Could you explain how its this answer – Earlier we asked for you to identify the other foreign key in the PersonPhone table. The correct answer is PhoneNumberTypeID

    I see it on the SQL screenshot you can just identify that it has as FK but I didn’t understand when looking at the two tables from the diagram above.

    Thank you

  • Wikiipedia says, “In the context of relational databases, a foreign key is a field (or collection of fields) in one table that uniquely identifies a row of another table or the same table.”

    In the question you pose, I’m still not understanding how PhoneNumberTypeID is the other foreign key. Logically, what row does it identify in the Person table?

    • Hi,

      There is a relationship between the tables PersonPhone and PhoneNumberType.

      The ForeignKey PersonPhone.PhoneNumberTypeID relates to the PrimaryKey PhoneNumberType.PhoneNumberTypeID.

  • Are we to assume this because PhoneNumberTypeID been named similarly to BusinessEntityID, with “ID” at the end? Otherwise I don’t see a reason to be certain that field is a primary key any other table, as the PhoneNumberType table you are referencing is not shown.

  • May i know how the PhoneNumberTypeID is a foreign key.Because it is not present in the parent table then how come it could be a foreign key?

  • Hi, there’s an error in summary table – I think that you can have zero foreign keys in a table, so “Number allowed for table” should be “zero or more”. Good article though!

  • I’m taking a SQL course at University of Michigan. This was a helpful article. Thank you for the visual comparison of Primary and Foreign keys. Go Blue!

  • I’m confused as to how is a foreign key. It doesn’t support all the conditions previously laid out. Thank you

  • can foreign key contain a value which does not refer to the primary key? and also can it have values which do not refer to the primary key?

    • A foreign key is a set of one or more columns in a table that refers to the “primary key in another table”. So foreign key Can’t contain a value which does not refer to the primary key, I am not agree that PhoneNumberTypeID is a foreign key.

  • {"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}

    Are YOU READY TO START LEARNING SQL SERVER, BUT DON'T KNOW WHERE TO START?

    Nothing is worse than, being excited to learn a new tool but not knowing where to start, wasting time learning the wrong features, and being overwhelmed .

    But it doesn't have to be this way.

    I'm Putting together a free email course to help you get started learning SQL Server.

    Name*
    Email*
    >