Essential to Expert Skills progression

Excel tutorials

The only Excel books you will ever need

  • The only constantly updated Excel 365 titles more…
  • Thoroughly covers Power Pivot and Power Query more…
  • Covers business intelligence and OLAP features more…
  • Simple, easy, understandable, 2-page lessons more…
  • Available as both printed books and e-Books more…
  • Used by schools, colleges and universities  more…
  • Available for 365, 2019, 2016 and Mac versions more…

Primary and Foreign Keys simply explained

Primary and Foreign keys are a very simple relational database concept.  Until very recently Excel users didn’t have to worry about relationships because Excel didn’t support them.  

This all changed when Microsoft added Power Pivot (an OLAP data modeling tool) and Get & Transform (a powerful Extract Transform and Load tool)  to Excel. Suddenly Excel users had to understand relational database theory to make use of the new tools.

If you are an Excel users and want to understand how primary and foreign keys fit into the big picture of creating an OLAP solution using Power Pivot you should read my my Excel Power Pivot 2-minute overview article first.  But if you are a database designer struggling with relational concepts (but with no interest in Excel) you’ll also get a lot out of this short article.

The article below is an unedited lesson from one of the 35 short focused lessons in my Excel Expert Skills book/e-book that enables students to master the Get & Transform (previously called Power Query) tool that is now included in all current Excel versions.

Leave me a comment if you find the article useful.

note

Virtually all of the world’s databases work like this

Dr Edgar F Codd (1923-2003) invented the relational database when working for IBM in the 1970’s. 

The first relational database products came to market in the late 1970’s and were quickly adopted by big business.

So good and great was Codd’s design that nothing better has been developed in over thirty years. 

The entire world of commerce is now powered by relational databases.

You’ll usually find a requirement to analyze data from a relational database in all but the very smallest enterprises.

note

Primary and foreign keys should have the same name

Any database designer worthy of the name will use the same name for the primary key column and the related foreign key column.

I’ve found that, in the real world of business, I often need to work with a badly designed database where this is not the case.  This makes the data a lot more difficult to work with.

anecdote

How meaningful primary keys almost stopped the Welsh from buying cars

Many years ago, I implemented a Europe-wide Business Intelligence solution for a very large automotive finance company.

I was not pleased (but unsurprised) to find that the database designer had used meaningful primary and foreign keys.

The designer had used a concatenation of the customer’s last name and date of birth as the primary key for the customer table.

The designer believed that the possibility of having two customers with the same last name and date of birth was extremely unlikely to ever happen.

In fact, it transpired that 13.5% of the Welsh population have a last name of Jones, meaning that it was certain to happen in Wales (and actually very likely to happen everywhere).

The database was, of course, re-designed to use meaningless primary keys.

Lesson 11-26: Understand primary and foreign keys

A set of tables that are related to each other are referred to as a relational database.  Some of Get & Transform’s most useful features require an understanding of basic relational database theory. 

What is a primary key?

A relational database consists of several tables, each containing data.  A database table is conceptually no different to an Excel table except that each row must have a unique primary key

Here’s an example of a table from a relational database:

The above example comes from a database table called Category.  It is good relational database design practice to name a primary key as the table name plus the letters ID.  This designer has followed best practice and called the primary key CategoryID.

The only important quality of a primary key is that all primary key values must be different (unique) within the primary key column.  That’s because the primary key is used (by a relationship) to identify a single, row in a table.  If there were two table rows with the same primary key, a relationship wouldn’t be able to correctly identify which single row was being referenced.

In the above example the primary key is a number but primary keys can consist of numbers, letters or both.

What is a foreign key?

Here is an example of two related tables from a relational database:

Primary and foreign keys - diagram showing key relationship

You can see how it is possible to determine that Aniseed Syrup is in the Condiments category: 

  • In the Product table, Aniseed Syrup has a CategoryID of 2.
  • The item in the Category table with a primary key (CategoryID) of 2 is Condiments.

While CategoryID is the primary key column in the Category table, it is a foreign key column within the Product table.

You can think of the CategoryID values in the Product table as belonging to the Category table, making them foreign keys in the Product table.

You can probably now see the wisdom of the naming convention for primary keys.  It makes it possible to spot which are the primary and foreign keys in a table at a glance.

  • The column named with the table name plus ID is the primary key.
  • Any other column name that is suffixed with ID (has ID at the end of the name) is a foreign key.
  • Any column not suffixed with ID is a regular data field containing information.

Meaningful and meaningless primary keys

In the above example, the primary key is a meaningless number.  The number 2 tells you nothing about any attribute of the Condiments category. It simply provides a way to find where the correct Condiments row is located within the table.

It is also possible to use a meaningful primary key (though a professional database designer would never do this).  For example, you could argue that because category names are unique, it is fine to use the CategoryName column in the above table as the primary key.

If you decided to use the category name as the primary key the tables would look like this:

Primary and foreign keys lesson - related table illustration
Primary and foreign keys lesson - table with foreign keys iillustration.

From a database design perspective, this is generally a bad idea

You’ll often find, however, that you need to create this type of relationship when creating relationships between tables that originate from different databases.  In this case, no formal primary key/foreign key relationship will exist.

For example, you may need to join a table from your corporate database containing customer addresses with a table detailing sales tax rates by state that you’ve downloaded from the Internet.  In this case you would need to create a primary key/foreign key relationship between the state columns in each table.

Learn Excel 365 Expert Skills with The Smart Method (fourth edition) - Front cover

This lesson is excerpted from the above book. 

This is the only up-to-date Excel book currently published and includes an entire session devoted to the new Dynamic Arrays features.

It is also the only book that will teach you absolutely every Excel skill including Power Pivot, OLAP and DAX.

Lessons in Session 11

Session Eleven: Create Get & Transform queries
Lesson 11‑1: Understand Get & Transform and ETL
Lesson 11‑2: Create a simple extract and load web query
Lesson 11‑3: Understand queries and connections
Lesson 11‑4: Move, remove, rename, filter and sort columns
Lesson 11‑5: Split delimited data
Lesson 11‑6: Understand data types
Lesson 11‑7: Create and use a linked data type
Lesson 11‑8: Add a linked data type field using a data card
Lesson 11‑9: Resolve geocoding errors in the geography linked data type
Lesson 11‑10: Understand Get & Transform data types
Lesson 11‑11: Specify data types
Lesson 11‑12: Understand steps and PQFL
Lesson 11‑13: Remove empty, error and top and bottom rows
Lesson 11‑14: Understand and work with null values
Lesson 11‑15: Transform date and time columns
Lesson 11‑16: Transform number columns
Lesson 11‑17: Add a custom calculated column
Lesson 11‑18: Create an aggregated
data query
Lesson 11‑19: Unpivot aggregated data
Lesson 11‑20: Work with multiple
queries
Lesson 11‑21: Create an append query
Lesson 11‑22: Understand Column From Examples
Lesson 11‑23: Use Column From Examples with selected source columns
Lesson 11‑24: Use Column From Examples to extract characters from strings
Lesson 11‑25: Appreciate the potential of Column From Examples
Lesson 11‑26: Understand primary and foreign keys
Lesson 11‑27: Link primary and foreign keys using XLOOKUP
Lesson 11‑28: Efficiently import data using a view
Lesson 11‑29: Understand linked table and pivot table refresh
Lesson 11‑30: Load a query directly into the PivotTable cache
Lesson 11‑31: Understand normal and de-normalized data
Lesson 11‑32: Create a simple two-table merged query
Lesson 11‑33: Create a five-table merged query
Lesson 11‑34: Create a merged query using fuzzy logic
Lesson 11‑35: Improve fuzzy logic results using a custom transformation table
Session 11: Exercise
Session 11: Exercise Answers

Related Articles

OLTP database example

Understand OLTP database design

A Power Pivot data model is an OLAP database. Business databases are OLTP databases. This article discusses how OLTP databases are structured.

Share this article

Share on facebook
Share on twitter
Share on linkedin
Share on stumbleupon
Share on email

Leave a Reply

Your email address will not be published.

thirteen − 9 =

Cross-lesson hyperlinks

In order to use all cross-lesson hyperlinks you will need to download the full e-book. The Expert Skills e-book contains 253 short lessons that will teach you everything there is to know about Excel.

When you download the e-book you’ll be able to access every lesson,  online or offline, on all of your devices, using Amazon’s free advanced e-book reader