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 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:
1 2 3 4 5 6 7 8 9 10 | 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) ) |
Let’s take a look at the following statement:
1 | CONSTRAINT fk_courseid FOREIGN KEY(courseid) REFERENCES courses(courseid) |
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:
1 2 3 | ALTER TABLE fk_tablename ADD FOREIGN KEY (fk) REFERENCES ref_tablename(fk) |
For example, you can create a foreign key in the batches
table as follows:
1 2 3 | ALTER TABLE batches ADD FOREIGN KEY (courseid) REFERENCES courses(courseid) |
Removing FOREIGN KEY constraint
To remove a FOREIGN KEY
constraint, you also use the ALTER TABLE
statement in the following form:
1 2 | ALTER TABLE fk_table DROP CONSTRAINT constraint_name |
For example, to remove the fk_courseid
FOREIGN KEY
constraint, you use the following statement:
1 2 | ALTER TABLE batches DROP CONSTRAINT fk_courseid |
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.