Primary Keys vs Foreign Keys

Before we begin with table relationships, we need to define these two terms. We start with the Primary Key, which has two definitions depending on how it is used. The most common explanation is that a Primary Key within a table is the column or columns that will uniquely identify each record in the table. For the tables noted above the CustomerID field will uniquely identify each record in the Customers table. OrderID will provide a unique identifier for each order in the Orders table. And finally, the ProductID column uniquely identifies the products in the Product table. (Here is a demonstration on creating table relationships).

However, the term Primary Key is also used within a relationship. Specifically, the Primary Key is the side of the relationship that best describes the field connecting or building the relationship between the two tables. The opposite side of the relationship will be the Foreign Key.

In our example above, we are joining the Customers and Orders table. The column they have in common is the CustomerID column for the Customers table and the CustID column from the Orders table. (These columns will have the same data type and column size in both tables.) Now determine which table best describes the field that is connecting or building the relationship between the two tables.

What if our CustomerID was 250? The Customers table would have a better description of that value because it can provide our FirstName, LastName, Company Name, and Phone. While the Orders table would only have a value of 250 in the CustID column. The Orders table only describes orders; it does not have any detailed information about customers.

In this case, the CustomerID column in the Customers table would be the Primary Key in the relationship and the CustID column in the Orders table would be Foreign Key in the relationship.

Parent table vs Child table

When building table relationships for database design or for joining tables, it is important to understand the Parent table to Child table relationships. When joining tables, the Parent table will be on the left-hand side of the relationship and the Child table will be on the right-hand side of the relationship.

To determine which is the Parent table and which is the Child table, look for the Primary Key and the Foreign Key of the relationship. The table that has the Primary Key in the relationship is the Parent table and the table that has the Foreign Key is the Child table.

Let’s look again at the relationship design above, but this time we will look at the Products and Orders tables. The relationship between the two tables is connected on the ProductID column of each table. Which table would best describe the connecting field?

The answer is the Products table because it provides the ProductName and Price of the ProductID, while the Orders table only has the value of the ProductID of each item purchased.

So, the ProductID column in the Products table would be the Primary Key in the relationship making the Products table the Parent table and on the left-hand side of the relationship.

The ProductID in the Orders table would then be the Foreign Key in the relationship. Since the Orders table has the Foreign Key that would make it the Child table and on the right-hand side of the relationship.

A final note, in the diagram above, the Products table is to the right of the Orders table. The position of a table in a relational diagram has no effect on the Parent table to the Child table relationship.