What is a Database Foreign Key

One Table's Foreign Key Links to Another Table's Primary Key

Format
mla apa chicago
Your Citation
Chapple, Mike. "What is a Database Foreign Key." ThoughtCo, Nov. 11, 2016, thoughtco.com/foreign-key-definition-1019255. Chapple, Mike. (2016, November 11). What is a Database Foreign Key. Retrieved from https://www.thoughtco.com/foreign-key-definition-1019255 Chapple, Mike. "What is a Database Foreign Key." ThoughtCo. https://www.thoughtco.com/foreign-key-definition-1019255 (accessed September 20, 2017).
Two men discussing a project
Musketeer/Digital Vision/Getty Images

 

Database designers make wide use of keys when developing relational databases. Among the most common of these keys are primary keys and foreign keys. A database foreign key is a field in a relational table that matches the primary key column of another table. To understand how a foreign key works, let's take a closer look at the idea of a relational database.

Some Basics on Relational Databases

In a relational database, data is stored in tables containing rows and columns, making it easy to search and manipulate.There is some serious math behind the concept of a relational database (relational algebra, proposed by E.F.

Codd at IBM in 1970), but that's not the topic of this article. 

For practical purposes (and non-mathematicians), a relational database stores "related" data in rows and columns. Further -- and here's where it gets interesting -- most databases are designed so that the data in one table can access the data in another table. This ability to create relationships between tables is the real power of a relational database. 

Using Foreign Keys

Most tables — especially those in large, complex databases, have primary keys. Tables that are designed to access other tables must also have a foreign key.

To use the commonly-cited Northwinds database, here is an excerpt of a Product table:

The Northwind Database's Product Table Excerpt
ProductIDProductNameCategoryIDQuantityPerUUnitPrice
1Chai110 boxes x 20 bags18.00
2Chang124 - 12 oz bottles19.00
3Aniseed Syrup212 - 550 ml bottles10.00
4Chef Anton's Cajun Seasoning248 - 6 oz jars22.00
5Chef Anton's Gumbo Mix236 boxes21.35
6Grandma's Boysenberry Spread212 - 8 oz jars25.00
7Uncle Bob's Organic Dried Pears712 - 1 lb pkgs.30.00

 

 

 

 

 

 

The ProductID column is this table's primary key. It assigns a unique ID to each product.

This table also contains a foreign key column, CategoryID. Every product in the Product table links to an entry in the Categories table that defines that product's category.

Note this excerpt from the database's Categories table:

Northwind Database's Categories Table Excerpt
CategoryIDCategoryNameDescription
1BeveragesSoft drinks, coffees, teas, beers, and ales
2CondimentsSweet and savory sauces, relishes, spreads, and seasonings
3ConfectionsDesserts, candies, and sweet breads
5Dairy ProductsCheeses

 

 

 

 

 

The column CategoryID is this column's primary key. (It has no foreign key because it has no need to access another table.) Every foreign key in the Product table links to a primary key in the Categories table. For example, the product Chai is assigned a category "Beverages", while Aniseed Syrup is in the category Condiments.

This kind of linking creates a myriad of ways to use and re-use data in a relational database.

For more on this topic, read Creating Foreign Keys.