Comparing Primary Keys VS Foreign Keys

Relational databases that we use to write our SQL database queries contain tables. These tables will almost undoubtedly contain keys. There are two types; primary and foreign keys. This is regardless of whether the data modeling style is OLTP, OLAP or some other style. Let’s look at the definitions and differences when comparing a primary key vs a foreign key.

What Are Primary Keys?

Each table has up to one primary key. Tables cannot contain more than one primary key and this key is a unique value. This means no two values are the same. The primary key is used as an identifier to uniquely signify each row. This is usually set by an automatically assigned number in sequential order, but this doesn’t have to be the case.

As an example, we have a database that has a customer table. If we want to know if it has a key we can easily check the table properties. In this case, the “CustomerId” field is the primary key as pictured below. Commonly we see primary keys identified as ‘PK_%’ or a similar prefix.

If we want to look at the key values we can write a simple “SELECT * FROM Customer;” query and return the results from the table. We can see the values are in sequential order. As mentioned, this is not always the case but is most common.

We commonly see primary key fields with the ‘key’ or ‘id’ name attached. This is just a common database practice and provides easier readability when identifying columns in a database table. In a nutshell, our primary key is a single tables’ unique identifier.

What Are Foreign Keys?

Contrary to a primary key, there can be many foreign keys in a table. These foreign keys are references to other tables in a database. When a table has a foreign key that means we are referencing a primary key of another table.

As an example, we have an invoice table that has a relationship to our customer table. Since the invoices are assigned to specific customers, our table has a “CustomerId” value. As shown below our primary key in this case would be the “InvoiceId” field. In this case, the “CustomerId” is our foreign key.

The primary to foreign key relationships are usually a one-to-many relationship meaning we can have the same customer related to several invoices. To rephrase, this means one customer to many invoices.

Final Thoughts – Primary vs Foreign Keys

In conclusion, we see that primary keys are unique identifiers to a single table where-as a foreign key is just a reference. As a result, we can have many foreign keys on a table but only one primary key. When comparing a primary key vs a foreign key we now know that for every foreign key there must exist a primary key.