Click here to Skip to main content
15,878,231 members
Articles / Productivity Apps and Services / Sharepoint

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

Rate me:
Please Sign up or sign in to vote.
4.31/5 (5 votes)
16 Jun 2015MIT4 min read 39.7K   2   3
Difference between primary and foreign keys, and why both are important to maintaining a relational database structure

In this article, we learn the difference between primary and foreign keys, and why both are important to maintaining a relational database structure.

All the examples for this lesson are based on Microsoft SQL Server Management Studio and the AdventureWorks2012 database.

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 is used to uniquely identify each row in the table. You can think of the primary key as an address. If the rows in a table were mailboxes, then the primary key would be the listing of street addresses.

When a primary key is composed of multiple columns, the data from each column is used to determine whether a row is unique.

In order to be a primary key, several conditions must hold true. First, as we mentioned, the columns must be unique. To clarify, we’re referring to the data within the rows, not the column names themselves. Also, no value in the columns can be blank or NULL.

When defining a table, you specify the primary key. A table has just one primary key, and its definition is mandatory.

The primary key for each table is stored in an index. The index is used to enforce the 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.

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 isn’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.

Image 1

Example of Foreign Key

Foreign Keys as Part of Primary Keys

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

Image 2

Foreign Key Value That is Part of Primary Key

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, in 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.

I agree this is confusing, but it is allowed and not a bad practice.

Unlike primary keys, foreign keys can contain duplicate values. Also, it is OK for them contain NULL values.

Indexes aren’t automatically created for foreign keys; however, as a DBA, you can define them.

A table is allowed to contain more than one foreign key. In the PersonPhone table, can you find the other foreign key (see answer at the end of the article)?

Finding Primary and Foreign Keys in Object Explorer

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.

Image 3

Finding Primary and Foreign Keys in Object Explorer

Also, if any of the foreign keys are defined in foreign key constraint, which we’ll learn about 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 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:

Image 4

Comparison of Primary Key to Foreign Key

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

License

This article, along with any associated source code and files, is licensed under The MIT License


Written By
Easy Computer Academy, LLC
United States United States
Hello my name is Kris. I’m here because I am passionate about helping non-techie people to overcome their fear of learning SQL.

I know what it is like to not know where to start or whether the time spent learning is worth the effort. That is why I am here to help you to:
- Get started in an easy to follow step-by-step manner.
- Use your time wisely so you focus on what is important to learn to get the most value from your time.
- Answer your questions. Really! Just post a comment and I’ll respond. I’m here to help.

It wasn’t long ago that I was helping a colleague with some reporting. She didn’t know where to start and soon got overwhelmed and lost as she didn’t know SQL.

I felt really bad, as she was under pressure to get some summary information to her boss, the built-in reports were falling short, and to make them better would require her to know SQL. At that time that seemed impossible! It in dawned on me, it doesn’t have to be that way.

Then I discovered a way for anyone with the desire to easily learn SQL. I worked with my co-worker, started to teach her what I learned and soon she was able to write reports and answer her boss’ questions without getting stressed or ploughing hours into manipulating data in Excel.

It hasn’t always been easy. Sometimes the information seems abstract or too conceptual. In this case I’ve found out that a visual explanation is best. I really like to use diagrams or videos to explain hard-to-grasp ideas.

Having video, pictures, and text really help to reinforce the point and enable learning.

And now I want to help you get the same results.

The first step is simple, click here http://www.essentialsql.com/get-started-with-sql-server/

Comments and Discussions

 
QuestionPK ..FK important Pin
Member 128344586-Nov-16 1:51
Member 128344586-Nov-16 1:51 
AnswerRe: PK ..FK important Pin
essentialSQL6-Nov-16 2:02
essentialSQL6-Nov-16 2:02 
If the primary key is being changed, and the table holding the FK is participating in another transaction, you'll get a dead lock.

To avoid this try to understand why the two transaction happen in the first place. Why are you updating child records in one process, and parent PKID's in another?
Kris - www.essentialsql.com

QuestionPrimary Key and Foreign Key? Pin
Camilo Reyes17-Jun-15 5:25
professionalCamilo Reyes17-Jun-15 5:25 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.