SQL Foreign Key

SQL Tutorials 7 min read
foreign key
Blog / SQL Tutorials / SQL Foreign Key

When creating relational schemas in SQL, we will often use a foreign key. If you are not familiar with one of these terms, feel free to clear that up by reading the linked article.

Now, let’s take a look at a table called “Sales”. It contains the number of a purchase, its date, the id of the customer and the item code.

Sales table with four columns, sql foreign key

Creating Another Table

The “customer_id” column does not give much information on the purchaser, does it? How about we create another table called “Customers” that consists of the first name, last name, email address, and number of complaints of each customer.

Customers table with four fields, sql foreign key

As you can see in the picture above, the table contains all these fields, as well as a “customer_id” column, which is unique; there is no need to keep the duplicate information about the same customer, right?

Customers table with unique customer id, sql foreign key

Therefore, in this table, the “customer_id” field is the primary key. The other columns represent the rest of the information about a customer.

data science training

Creating the Relation

We already have two tables – “Sales” and “Customers”! We know they are related through the “customer_id” field, and as you can see in the picture below, a “customer_id” column exists in the “Sales” table.

The relation between the two table is the customer ID, sql foreign key

The similarity of the two column names is chosen purely for convenience. The same relationship could be based on columns with different names – “customer_id” and just “id”, for example.

The relating columns don't need the exact same name, sql foreign key

Important: The information in the “Sales” table can be obtained through the “customer_id” column in the “Customers” table.

Let’s stick to using the same name for both columns.

SQL Foreign Key in Relational Schemas

Here is the big trick. “customer_id” is a primary key for the “Customers” table and a foreign key in the “Sales” table.

The customer Id is the primary key for the customer table but the foreign key for the sales table

In the relational schemas form of representation, relations between tables are expressed in the following way – the column name that designates the logical match is a foreign key in one table, and it is connected with a corresponding column from another table.

The relation is often foreign key to primary key

Often, the relationship goes from a foreign key to a primary key, but in more advanced circumstances, this will not be the case. To catch the relations on which a database is built, always look for the foreign keys, as they show us where the relations are.

How to Describe it

1. You should indicate that “customer_id” is a foreign key in the “Sales” table with (FK) next to the column name.

Indicate the foreign key with FK2. You must draw an arrow that begins from the same level as the field name of the foreign key and ends pointing at the column name from the table it is related to. Don’t worry that the arrow won’t be straight what matters are its start and its end just like the one in the picture below.

Connect the with an arrow

How to Distinguish a Foreign from a Primary Key?

SQL Primary Key

Observe the following example from our “Sales” database to avoid confusion between primary and foreign keys. In the “Customers” table, “customer_id” contains different values. Moreover, different numbers are stored in every row, and there are no missing values. This complies with the requirement for primary keys to contain unique values only.

Unique values only in the primary key

SQL Foreign Key

In the “Sales” table, though, we see under the “customer_id” column we can have repeating and missing values. This is okay. For instance, it is normal to expect customer number 2 to have executed three purchases in our shop.

The foreign key can have repeating values

Furthermore, it is possible to not have who executed purchase number 9 registered, as indicated in the picture below. On the contrary, when using a primary key, we cannot leave a blank cell.

primary cannot have a blank cell

The values in this field in the “Sales” table will be mentioned in the “Customers” table, as well.

The values will be mentioned in both tables

Avoiding Errors

If we have registered information for four customers with IDs 1,2,3, and 4, we cannot have a value of 5, 10, or 100 in the “customer_id” field in the “Sales” table. This also means, if you try to insert a record with a “customer_id” that differs from the ones we have already seen in the “Customers” table, MySQL will raise an error and the record won’t be stored.

If values differ, sql will raise an error

Creating a Third Table

By the same token, we can create an “Items” table. The field “Item_code” appears in this table as a primary key and in “Sales” as a foreign key.

In an items table the item code is the primary key and the foreign key in the sales table

Analogically to the previous example with the “customer_id” column from the “Customers” table, “item_code” contains a letter and a number forming a unique combination in the “Items” table. While in “Sales”, the values could repeat, as is the case with item B2, for instance.

The values are unique in the items table and can repeat in the sales table

Putting it into a Relational Schema

Therefore, we will use (FK) to indicate an “item_code” is a foreign key in the “Sales” table. We can achieve that by drawing an arrow starting from the “item_code” field in the “Sales” table pointing at the column with the same name in the “Items” table.

Add FK to item code in the sale table and draw an arrow to the primary key in the items table

Considering all we’ve said so far, foreign keys, similar to primary keys, can be called identifiers. However, they identify the relationships between tables, not the tables themselves.

Why We Need Foreign Keys

To sum up, in order to express the “relational” from the term “relational database”, we can use a foreign key. It serves a great job in connecting different tables via their similar fields. Fields that don’t necessarily need to be the same. So, if you are interested in learning more about relational schemas, unique keys and null values then jump to our next tutorial

***

Eager to hone your SQL skills? Learn how to put theory into practice with our hands-on tutorials!

Next Tutorial: Unique Key and Null Values

Earn your Data Science Degree

Expert instructions, unmatched support and a verified certificate upon completion!

Leave a Reply

Your email address will not be published.

×
LAST CHANCE
Ready to Learn Data Science?
50% OFF