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.
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.
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?
Therefore, in this table, the “customer_id” field is the primary key. The other columns represent the rest of the information about a customer.
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 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.
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.
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.
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.
2. 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.
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.
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.
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.
The values in this field in the “Sales” table will be mentioned in the “Customers” table, as well.
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.
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.
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.
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.
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? Take our SQL course.
Next Tutorial: Unique Key and Null Values
