What is a foreign key constraint? Why is it important in relational databases? Find out all about foreign keys in this article.
A foreign key is a concept that is often used in relational databases. It is a way to create a link between two different tables. A foreign key is a field that refers to another table‘s primary key. Look at the example below: each
player is a member of one
team. The field
team_id points to an object in the
Foreign keys are important because they ensure data integrity. When inserting a new player or updating a current one, a correct
team_id must be provided (or it must be null, if the field is nullable). Otherwise, the operation will fail.
What Is the Difference Between a Primary Key and a Foreign Key?
The goal of the primary key is to make sure that the data in one column (or a set of columns, in the case of a composite key) is unique. This ensures that a specific row can always be identified; its primary key differs from all other primary keys in that table. A primary key can be a column that already exists in the table and is always unique, such as a student’s ID card number. Such a key is called a natural key. However, it is also a common practice to use a surrogate key instead. A surrogate key is not part of the actual data; its only purpose is to be a unique row identifier within the database. You can read more about candidate keys and how to choose a good primary key in our blog.
When referring to the tables in a primary key–foreign key relationship, it’s often said that the primary key is in the parent table and the foreign key is in the child table. This is because the foreign key depends on the values in the primary key.
Foreign Key Constraints
To create a foreign key column, a foreign key constraint must be used. It can be declared when creating the table. The actual SQL code can differ among various database types. For a PostgreSQL database, we would use the following
CREATE TABLE statement:
CREATE TABLE player ( id int NOT NULL, name varchar(255) NOT NULL, team_id int NOT NULL REFERENCES team (id), CONSTRAINT player_id PRIMARY KEY (id) ); CREATE TABLE team ( id int NOT NULL, name varchar(255) NOT NULL, CONSTRAINT team_id PRIMARY KEY (id) );
ALTER TABLE statement:
We can also define a foreign key constraint using a separate
ALTER TABLE player ADD CONSTRAINT player_team FOREIGN KEY (team_id) REFERENCES team (id);
Composite Foreign Keys
Like a primary key constraint, a foreign key constraint can be based on a single column or on multiple columns. Using multiple columns is known as a composite key. Composite keys are often used when the primary key is based on existing columns instead of a surrogate key.
In the example below, the
address table has a composite primary key that consists of three columns:
city. To establish a foreign key relationship between the
address tables, the
apartment table must have three columns:
address_building_number. These three columns together form a composite foreign key.
If a composite primary key consists of three columns, then a foreign key constraint must also use these three columns. This means that the child table will have all of those three columns and use them as a composite foreign key to establish the relationship.
Foreign Key Constraint Cardinality
Foreign key constraints have a feature called cardinality. Cardinality defines the number of entities involved in a relationship:
Look at the examples below. If a book can have only one author and an author only one book, then the
author tables have a one-to-one relationship. This can be achieved by setting a
UNIQUE constraint on the foreign key (
author_id column) in the
If an author can write many books, then multiple rows in the
book table can refer to the same row in the
author table. This means that we have a many-to-one relationship. It happens if the foreign key column is not declared as
Many-to-many relationships are the most complicated, as they require an additional table. Below, the
author tables are connected indirectly via the
book_author table. This table contains two foreign keys – one referring to the
book table and another referring to the
author table. Thanks to this solution, authors can write multiple books and books can have more than one author. All the links between
author tables are stored in the
Handling Update and Delete Operations Involving Foreign Keys
Imagine that one of the rows in the parent table is updated or deleted. What happens to the associated rows in the child table? There are different options that can be configured for foreign keys when the parent table is changed. They are:
ON UPDATE/DELETE CASCADE
ON UPDATE/DELETE RESTRICT
ON UPDATE/DELETE NO ACTION
ON UPDATE/DELETE SET NULL
ON UPDATE/DELETE SET DEFAULT
These options can be configured when declaring the foreign key:
ALTER TABLE player ADD CONSTRAINT player_team FOREIGN KEY (team_id) REFERENCES team (id) ON UPDATE
Note that these constraints are not mandatory. Let’s see what each one does.
If the primary key value is updated in the primary table, the child table gets updated as well. If the row in the primary table is deleted, all the rows referencing it in the child table are also removed.
Primary key values cannot be modified in the primary table. Also, if the row in the primary key table is referenced by any rows in the child table, then this row cannot be deleted unless the rows in the child table are removed first. If a user tries to perform such an action, an error will be thrown.
This is the default constraint. In some databases, it is the same as RESTRICT. In other databases (like PostgreSQL), you can defer the constraint. In this case, the error will be produced at constraint check time (e.g. when the transaction is complete) if there are any associated rows in the child table.
If the primary key in the parent table is updated or the whole row is deleted, the associated rows in the child table will have the foreign key column set to NULL.
If the primary key in the parent table is updated or the whole row is deleted, the associated rows in the child table will have the foreign key column set to its default value. Configuring a default value would look like this:
ALTER TABLE player ADD CONSTRAINT player_team FOREIGN KEY (team_id) REFERENCES team (id) ON UPDATE SET DEFAULT 1
Want to Learn More About Foreign Key Constraints?
Now you know what a foreign key is and why it is important in relational databases. We’ve discussed some advanced features of foreign keys; if you want to learn more, make sure to check out our article What Is the Benefit of Foreign Keys in SQL?