To allow the association of data in relational databases, rows should be uniquely identifiable. When defining a new table, there are two types of constraints that can be declared, PRIMARY KEYs and FOREIGN KEYs.
A PRIMARY KEY is a field or a combination of fields that is used to uniquely define a record (row). PRIMARY KEYs can be defined when creating a table or they can be added at a later time.
Let’s start by creating a simple table students with 2 columns, id and name. We want to identify each student by their id, meaning that we will define this column as a PRIMARY KEY. To do this, you would have to write:
Defining a column as a PRIMARY KEY implies that you cannot insert NULL values in said column and that all the values must be unique. If that was not the case, you wouldn’t be able to use the PRIMARY KEY to easily identify each row.
To ensure that a column can’t take any NULL values you can use the NOT NULL constraint when defining your table. Here is an example:
Let’s see what happens when we insert the following values in the students table:
Foreign Keys
The FOREIGN KEY is the main principle behind RDBMS¹. It represents a directed reference from one table (usually called the child) to another table (the parent).
This relationship is only possible when the column from the child table and the column from the parent table contain identical values.
The FOREIGN KEY relates one row from the child with a single row from the parent, but a row from the parent table can be related to many rows from the child table.
Consider the following tables:
employees
departments
As with PRIMARY KEYs, FOREIGN KEYs can be defined when creating a table or added at a later time.
For the employees and departments table, we know that the department_id and the id columns contain identical values. We would then like to create a reference between these tables. This would be done as such:
Defining constraints such as a FOREIGN KEY usually starts with CONSTRAINT followed by the constraint name, which in our case is department_fk.
Following this is the type of constraint, FOREIGN KEY, the name of the column, department_id, which REFERENCES another table, departments, and in parentheses is the parent*column name, id.
Note that the column from the parent table must be defined as either PRIMARY KEY or UNIQUE² for the relationship to work.
Footnotes
[1] RDBMS stands for Relational Database Management System.
[2] The UNIQUE constraint is very similar to PRIMARY KEY but it has two small differences. The first is that a column defined as UNIQUE can hold NULL values, and because the expression NULL = NULL doesn’t evaluate to true (it evaluates to unknown) the column can hold multiple NULL values. The second difference is that there can only be one column defined as PRIMARY KEY, but there can be many columns defined as UNIQUE.
