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.