FOREIGN KEY Constraint

Summary: in this tutorial, we will discuss the FOREIGN KEY constraint that enforces a link between the data in two tables.

Introduction to foreign key

A foreign key is a column or combination of columns used to enforce a relationship between data in two tables. The table that contains the foreign key is called foreign key table.

Let’s take a look at the following tables:

SQL FOREIGN KEY constraint - two tables

One course has zero or more batches, however, a batch has to belong to one course. This relationship is defined using courseid foreign key column. The batches table is called foreign key table.

To create a foreign key, you use a  FOREIGN KEY constraint.

Creating FOREIGN KEY constraint

You can create a  FOREIGN KEY constraint when you create or modify a table.

Creating FOREIGN KEY constraint using CREATE TABLE statement

The following query creates a  FOREIGN KEY constraint using the CREATE TABLE statement:

Let’s take a look at the following statement:

We defined the courseid column in the batches table as a foreign key that references to the courseid column in the courses table. We assigned a name ( fk_courseid) to the foreign key constraint so that we can use this name to remove the constraint later if we want.

Notice that the  CONSTRAINT fk_courseid is optional. If you omit it, the database engine will generate a name for the constraint.

Creating FOREIGN KEY constraint using ALTER TABLE statement

In case you want to create a  FOREIGN KEY constraint in an existing table, you can use the ALTER TABLE statement as follows:

For example, you can create a foreign key in the batches table as follows:

Removing FOREIGN KEY constraint

To remove a  FOREIGN KEY constraint, you also use the  ALTER TABLE statement in the following form:

For example, to remove the  fk_courseid  FOREIGN KEY constraint, you use the following statement:

In this tutorial, we have explained the foreign key constraint concept and shown you how to manage foreign key constraints including creating, adding and removing the constraints.