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:

One course has zero or more batches, however, a batch has to belong to one course. This relationship is defined using courseidforeign key column. The batchestable 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:

CREATE TABLE batches (
  batchid INT NOT NULL,
  batchname VARCHAR(255) NOT NULL,
  begindate DATE NOT NULL,
  enddate DATE NOT NULL,
  courseid INT NOT NULL,
  PRIMARY KEY (batchid),
  CONSTRAINT fk_courseid FOREIGN KEY(courseid) 
  REFERENCES courses(courseid)
)Code language: SQL (Structured Query Language) (sql)

Let’s take a look at the following statement:

CONSTRAINT fk_courseid FOREIGN KEY(courseid) REFERENCES courses(courseid)Code language: SQL (Structured Query Language) (sql)

We defined the courseidcolumn in the batchestable as a foreign key that references to the courseidcolumn in the coursestable. 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:

ALTER TABLE fk_tablename
ADD FOREIGN KEY (fk)
REFERENCES ref_tablename(fk)Code language: SQL (Structured Query Language) (sql)

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

ALTER TABLE batches 
ADD FOREIGN KEY (courseid)
REFERENCES courses(courseid)Code language: SQL (Structured Query Language) (sql)

Removing FOREIGN KEY constraint

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

ALTER TABLE fk_table
DROP CONSTRAINT constraint_nameCode language: SQL (Structured Query Language) (sql)

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

ALTER TABLE batches
DROP CONSTRAINT fk_courseidCode language: SQL (Structured Query Language) (sql)

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.