What Is a Foreign Key?

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 team table.

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)
);

We can also define a foreign key constraint using a separate ALTER TABLE statement:

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: street, building_number and city. To establish a foreign key relationship between the apartment and address tables, the apartment table must have three columns: address_street, address_city, and 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:

• One-to-one
• Many-to-one
• Many-to-many

Look at the examples below. If a book can have only one author and an author only one book, then the book and 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 book table.

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 UNIQUE.

Many-to-many relationships are the most complicated, as they require an additional table. Below, the book and 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 book and author tables are stored in the book_author table.

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 
	ON DELETE ;

Note that these constraints are not mandatory. Let’s see what each one does.

CASCADE

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.

RESTRICT

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.

NO ACTION

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.

SET 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 NULL.

SET DEFAULT

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?