What Is the Benefit of Foreign Keys in SQL?

What is a foreign key and why is it so important in relational databases? Learn all the details about foreign keys in this article.

One of relational databases’ key features is the ability to link data stored in different tables. These links, called references, essentially act as connections between tables. They are created using the FOREIGN KEY constraint on table columns.

In this article, we’ll learn what the FOREIGN KEY constraint does in SQL. I’ll explain how to define it using the CREATE TABLE statement and we’ll go through some examples. Next, we’ll talk about its benefits and features. Specifically:

• We’ll learn about the relationship between the primary table (which provides its primary key column(s) values to the foreign table) and the foreign table (which uses the column(s) provided by the primary table as its foreign key).
• We’ll talk about what happens when the primary table column values get deleted or changed. There are various options offered by the FOREIGN KEY constraint in such situations – I’ll clarify them by including some examples.
• At the end, we’ll discuss the cardinality options that can be implemented with the FOREIGN KEY

Let’s get started.

What Is a Foreign Key Constraint in SQL?

To understand the concept of the FOREIGN KEY constraint in SQL, you can think of it as a reference link between tables that are known as the primary (or parent) and foreign (or child) tables. The foreign table references one or more columns (the primary key, which can be one or more columns) in the primary table; that is how the link is created. If you need to refresh your knowledge of primary keys in SQL, I recommend reading the article What Is a Primary Key?.

An Example in Pictures

I believe some visual aids might be helpful here. Let’s look at the images below.

Here, we’ve got the Airplane table (the primary table) and the Flight table (the foreign table). The AirplaneId column, which is a primary key column for the Airplane table, is used as a foreign key column in the Flight table. That is how the relation is created between these tables – the AirplaneId column of the Flight table defines which airplane is used for each flight.

Please note that the AirplaneId column of the foreign table Flight does not need to contain all the values stored in the Airplane.AirplaneId. It may contain a subset of these values:

The values contained in the Flight.AirplaneId column directly refer to the values contained in the Airplane.AirplaneId column. Hence, any update or delete actions on the rows in the primary table Airplane must be reflected accordingly by the foreign table Flight. The FOREIGN KEY constraint offers various options to implement these updates or deletions, which will be discussed later on.

And one more important thing. The above example shows a relation between the Airplane and Flight tables using only one column. It is also possible to use multiple columns to create such relations between tables.

Here, instead of using the AirplaneId column for the FOREIGN KEY constraint, we have decided to use the AirplaneBrand and AirplaneModel columns, as they also uniquely identify each row of the Airplane table (assuming that our airline owns only one airplane of each brand-model pair listed in the table).

You may notice that we could as well place all the data into one table called AirplaneFlight. That is true; however, then we wouldn’t be able to segregate the data into different categories within one table. That’s the reason why foreign keys are such a crucial part of database design. They allow us to place related data into multiple tables and then link them together to keep their integrity.

How to Define the Foreign Key Constraint in SQL

Now we want to bring our design into the database. We can do so by using the CREATE TABLE statement and defining the FOREIGN KEY constraint within it.

Let’s see how the CREATE TABLE statement looks if we define the FOREIGN KEY constraint based on the example above, which uses the column AirplaneId.

First, we must create the primary table and define its primary key column.

CREATE TABLE Airplane (
AirplaneId VARCHAR(10) NOT NULL,
AirplaneBrand VARCHAR(30) NOT NULL,
AirplaneModel VARCHAR(30) NOT NULL,
CONSTRAINT PK_AirplaneId PRIMARY KEY (AirplaneId)
);

To review the PRIMARY KEY constraint, check out What is a Primary Key in SQL?

Next, we create the foreign table:

CREATE TABLE Flight (
FlightId VARCHAR(10) NOT NULL,
AirplaneId VARCHAR(10) NOT NULL,
PilotId INTEGER NOT NULL,
CONSTRAINT PK_FlightId PRIMARY KEY (FlightId),
CONSTRAINT FK_AirplaneId FOREIGN KEY (AirplaneId)
REFERENCES Airplane(AirplaneId)
);

Here, apart from the PRIMARY KEY constraint, we have defined the FOREIGN KEY constraint. We gave it the name FK_AirplaneId. Within brackets, we defined the column of the Flight table that implements the FOREIGN KEY constraint. After the REFERENCES keyword, the primary table name and a column follow.

That’s how we can move our database design concept into a workable database.

It can happen that we want to add the FOREIGN KEY constraint to the Flight table after the table has been created. To do that, we use the ALTER TABLE statement:

ALTER TABLE Flight ADD FOREIGN KEY FK_AirplaneId (AirplaneId)
REFERENCES Airplane(AirplaneId);

We may also want to drop a FOREIGN KEY constraint. Suppose we want to change the FOREIGN KEY constraint defined above to its multicolumn version. We must drop it first and then create the new key:

ALTER TABLE Flight DROP FOREIGN KEY FK_AirplaneId;
ALTER TABLE Flight ADD FOREIGN KEY FK_Airplane (AirplaneBrand, AirplaneModel)
REFERENCES Airplane(AirplaneBrand, AirplaneModel);

Now, the >Flight table uses the multicolumn FOREIGN KEY constraint.

If you feel that you need more practice with creating database tables, check out our course on The Basics of Creating Tables in SQL.

What Are the Benefits of Foreign Keys?

Now that we’ve learned all the basics of the FOREIGN KEY constraint, its features, and the possibilities it provides for database functionality, we can dive deeper into its benefits.

As I already mentioned, when implementing a FOREIGN KEY constraint, we can distinguish between the primary table and the foreign table.

The primary table provides a column, or a set of columns, that is used by the foreign table. In other words, the foreign table’s column (or set of columns) references a column (or set of columns) in the primary table.

So, the first benefit of the FOREIGN KEY constraint is that it ensures the existence of the referenced row in the primary table. If the referenced row is not in the primary table, it cannot be present in the foreign table.

Another benefit of the FOREIGN KEY (FK) constraint is that it ensures the correctness of the reference even when the primary table values are modified or deleted. There are a variety of actions that indicate what to do on the update or deletion of a primary key (PK) row. Let’s see what these actions are.

When a Primary Key Row Is Deleted

There are a number of options offered by the FOREIGN KEY constraint regarding what to do on the deletion of primary key row(s) from the primary table.

By default, when no action is specified for the FOREIGN KEY constraint, you can’t delete the relevant row in the primary (parent) table if there are rows that reference it in the foreign (child) table.

You can specify the action to take on the deletion of the primary table’s PK row(s) by choosing one of the following:

• ON DELETE CASCADE
• ON DELETE RESTRICT
• ON DELETE NO ACTION
• ON DELETE SET NULL
• ON DELETE SET DEFAULT

Let’s go through each of them. First, we’ll look at the syntax that all these options share.

ON DELETE Syntax

The declaration of the ON DELETE option follows directly after the declaration of the FOREIGN KEY constraint.

ALTER TABLE Flight ADD FOREIGN KEY FK_AirplaneId (AirplaneId)
REFERENCES Airplane(AirplaneId)
ON DELETE ;

Now let’s look at all the options.

ON DELETE CASCADE

The ON DELETE CASCADE option ensures that when a row is removed from the primary table’s PK, the row from the foreign table that references it is removed as well.

Let’s see it in action using our Airplane and Flight tables. First, we have the two related tables:

We remove one row from the primary table:

And now, the row that references the deleted row gets deleted from the foreign table.

To implement the ON DELETE CASCADE option, add it after the FOREIGN KEY constraint declaration:

ALTER TABLE Flight ADD FOREIGN KEY FK_AirplaneId (AirplaneId)
REFERENCES Airplane(AirplaneId)
ON DELETE CASCADE;

You should think of implementing the ON DELETE CASCADE option with the FOREIGN KEY constraint when you want:

• The ability to remove rows from the primary table without any restrictions.
• Automatic deletion of the rows in the foreign table when the reference row from the primary table is deleted.

ON DELETE RESTRICT

The ON DELETE RESTRICT option does not allow the deletion of primary key rows in the primary table. When such an action is attempted, an error is returned.

This is useful to prevent any undesired changes to the primary and foreign tables.

ON DELETE NO ACTION

The ON DELETE NO ACTION option is similar to the ON DELETE RESTRICT option; it also prevents the deletion of the primary table’s rows and throws an error if such an action is attempted.

The difference between ON DELETE NO ACTION and ON DELETE RESTRICT is that in some databases ON DELETE NO ACTION can be deferred, i.e. it can be postponed until after the transaction so that the eventual error is thrown after the transaction completes –i.e. in the course of the transaction the value of the foreign key can be fixed, otherwise the error is thrown.

ON DELETE SET NULL

The ON DELETE SET NULL option does exactly what it says. When a row is deleted from the primary table, the column values that reference it in the foreign table are set to NULL.

Let’s look at an example below. Here are the two tables again.

We remove one row from the primary table.

And now, the AirplaneId column value of the row that references the deleted row is set to NULL in the foreign table.

The ON DELETE SET NULL option can be used when you want to allow row deletion in the primary table but you do not want it to get propagated into the foreign table. There is a footprint of the deletion in the foreign table (the value marked NULL).

ON DELETE SET DEFAULT

The ON DELETE SET DEFAULT option is somewhat similar to the ON DELETE SET NULL option. Here, after the row deletion in the primary table, we set the foreign table’s FK column value to its default value instead of NULL.

Please note that a default value must be specified for the foreign key column(s).

Let’s specify a default value for the AirplaneId column of the Flight table:

ALTER TABLE Flight
ALTER AirplaneId
SET DEFAULT 'ABA340';

Now we can use the ON DELETE SET DEFAULT option. After the deletion of the primary table’s row, the following will happen:

You should use the ON DELETE SET DEFAULT option when you want to allow row deletion in the primary table. The degree of control here is that you can choose what value is inserted into the foreign table on the row deletion in the primary table.

When a Primary Key Row Is Updated

The actions offered by the FOREIGN KEY constraint on the update performed in the primary table are similar to the actions on delete. So is the syntax.

ON UPDATE Syntax

The declaration of the ON UPDATE option follows directly after the declaration of the FOREIGN KEY constraint.

ALTER TABLE Flight ADD FOREIGN KEY FK_AirplaneId (AirplaneId)
REFERENCES Airplane(AirplaneId)
ON UPDATE ;

By default, when no action is specified for the FOREIGN KEY constraint, it is not possible to modify the primary (parent) table rows if there are rows that reference it in the foreign (child) table.

You can define what happens on the update of the primary table’s row(s) by choosing one of the following:

• ON UPDATE CASCADE
• ON UPDATE RESTRICT
• ON UPDATE NO ACTION
• ON UPDATE SET NULL
• ON UPDATE SET DEFAULT

Let’s go through each of them.

ON UPDATE CASCADE

The ON UPDATE CASCADE option allows you to modify the values in the PK column(s) of the primary table. These changes are then propagated to the foreign table.

Let’s see it in action using our Airplane and Flight tables:

We modify the values in the last row of the primary table.

And now, the row that references it gets updated as well.

To implement the ON UPDATE CASCADE option, add it after the FOREIGN KEY constraint declaration:

ALTER TABLE Flight ADD FOREIGN KEY FK_AirplaneId (AirplaneId)
REFERENCES Airplane(AirplaneId)
ON UPDATE CASCADE;

This option allows modification to the primary table’s primary key column(s). Any modifications are then propagated to the foreign key(s).

ON UPDATE RESTRICT

If you want to prevent any modifications to the primary table’s PK, use the ON UPDATE RESTRICT option.

On any attempt to modify the primary table’s PK values, an error is returned.

ON UPDATE NO ACTION

The ON UPDATE NO ACTION option is similar to the ON UPDATE RESTRICT option. No changes to the primary table’s PK are allowed.

The difference is that some databases allow the actions checks to be postponed until after the transaction with ON UPDATE NO ACTION. If the primary table’s PK values are changed when the transaction completes, an error is thrown.

ON UPDATE SET NULL

The ON UPDATE SET NULL option allows modifications to the primary table’s PK. On such updates, the values in the foreign table’s FK are set to NULL.

Let’s look at an example below, starting with the two tables:

We modify the values in the last row of the primary table.

And now, the row that references it is set to NULL.

ON UPDATE SET DEFAULT

The ON UPDATE SET DEFAULT option allows modifications to the primary table’s PK. As a result, the values in the foreign key get set to their default value. You can only use this option if a default value is declared for the foreign key column(s).

Let’s specify the default value for the AirplaneId column of the Flight table:

ALTER TABLE Flight
ALTER AirplaneId
SET DEFAULT 'ABA340';

Now, we can use the ON UPDATE SET DEFAULT option. After the update to the primary table row, the following will happen:

Foreign Key Constraint Cardinality

Another feature offered by the FOREIGN KEY constraint is its cardinality. By setting cardinality, we can decide how the foreign key column(s) relate to the primary key column(s). Relationships can be:

• One-to-one (one PK row value can appear in exactly one FK row).
• Many-to-one (one PK row value can appear in one or many FK rows).
• Many-to-many (many PK row values can appear in many FK rows).

To implement the one-to-one relationship, we should declare the foreign key column(s) as UNIQUE. In this case, the relationship is actually (one or zero)-to-one, but it ensures that there is at most one row in the foreign table that relates to a particular row of the primary table.

On the other hand, if the foreign key column(s) of the foreign table are not declared as UNIQUE, then the relationship between the foreign key column(s) and primary key column(s) is many-to-one – many rows of the foreign table can relate to a single row of the primary table.

The many-to-many relationship is implemented using a separate table that stores each pair of related rows.

Notice that when we created this many-to-many relationship, the direct link between the Airplane and Flight tables changes into a separate table that stores all the relations in AirplaneId-FlightId pairs.

The AirplaneFlight table has a foreign key (AirplaneId) that refers to the AirplaneId column of the Airplane table. It also has a foreign key (FlightId) that refers to the FlightId column of the Flight table. The Airplane and Flight tables are now indirectly linked with each other.

Want to Learn More About Foreign Keys in SQL?

The FOREIGN KEY constraint is crucial to relational database design. It lets us link the data according to our needs. As it creates some dependencies between the columns of primary and foreign tables, it also lets us decide what to do ON UPDATE and ON DELETE actions performed on the rows of the primary table.

By using the FOREIGN KEY constraint, we ensure the integrity, correctness, and compactness of data. The values used by the foreign table must exist in the primary table. Also, we can impose rules on the kind of relationship, i.e. one-to-one, many-to-one, or many-to-many.

If you want to learn even more about foreign keys, check out our article on What is a Foreign Key in SQL?

To get more insights about database structure and design, I recommend our track on Creating Database Structure, which includes all the essentials. You can read about this track here before jumping into it.