How To Software The Power of Foreign Keys in Relational Databases A foreign key opens the door to a whole world of data Share Pin Email Print Musketeer/Digital Vision/Getty Images Software Databases Documents Spreadsheets Presentations Desktop Publishing Graphic Design Animation & Video by Mike Chapple An IT professional with more than 10 years of experience in the fields of databases and cybersecurity. Updated November 20, 2018 24 24 people found this article helpful 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 of 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 from a Product table: ProductID ProductName CategoryID QuantityPerU UnitPrice 1 Chai 1 10 boxes x 20 bags 18.00 2 Chang 1 24 - 12 oz bottles 19.00 3 Aniseed Syrup 2 12 - 550 ml bottles 10.00 4 Chef Anton's Cajun Seasoning 2 48 - 6 oz jars 22.00 5 Chef Anton's Gumbo Mix 2 36 boxes 21.35 6 Grandma's Boysenberry Spread 2 12 - 8 oz jars 25.00 7 Uncle Bob's Organic Dried Pears 7 12 - 1 lb pkgs. 30.00 The Northwind Database's Product Table Excerpt 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: CategoryID CategoryName Description 1 Beverages Soft drinks, coffees, teas, beers, and ales 2 Condiments Sweet and savory sauces, relishes, spreads, and seasonings 3 Confections Desserts, candies, and sweetbreads 5 Dairy Products Cheeses Northwind Database's Categories Table Excerpt 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. Continue Reading The SQL COUNT Function Does Exactly What You Think: It Counts What Are Some Keys Take Make DB Management Easier? Designing a Database? Give Your Tables Attributes to Be Proud Of Understanding the Types of Database Relationships How to Select Data Within Ranges in SQL Ditch Your Spreadsheet for a Database to Access the Power of your Data Here's How to Create Foreign Keys in Microsoft SQL Server How to Choose a Primary Key for Your Database Primary Keys in a Database and What Makes a Good Key Database Terms You Need to Know Relationships Are the Basis for Relational Databases A Database Relation Might Not Be What You Think An Intro to Databases That's Suitable for the Brand New Beginner How BCNF Is Used in Relational Database Theory The Role of Referential Integrity in Your Database Are You Making These Common Mistakes With Your Database Design?