SQL FOREIGN KEY

In this tutorial, you  will learn about the SQL foreign key and how to create a FOREIGN KEY constraint to enforce the relationship between tables.

Introduction to SQL foreign key constraint

A foreign key is a column or a group of columns that enforces a link between the data in two tables. In a foreign key reference, the primary key column (or columns) of the first table is referenced by the column (or columns) of the second table. The column (or columns) of the second table becomes the foreign key.

You use the FOREIGN KEY constraint to create a foreign key when you create or alter table. Let’s take a simple example to get a better understanding.

SQL FOREIGN KEY constraint examples

See the following projects and project_assignments tables:

CREATE TABLE projects (
    project_id INT AUTO_INCREMENT PRIMARY KEY,
    project_name VARCHAR(255),
    start_date DATE NOT NULL,
    end_date DATE NOT NULL
);

CREATE TABLE project_milestones(
    milestone_id INT AUTO_INCREMENT PRIMARY KEY,
    project_id INT,
    milestone_name VARCHAR(100)
);

Each project may have zero or more milestones while one milestone must belong to one and only one project. The application that uses these tables must ensure that for each row in the project_milestones table there exists the corresponding row in the projects table. In other words, a milestone cannot exist without a project.

Unfortunately, users may edit the database using client tool or if there is a bug in the application, a row might be added to the project_milestones table that does not correspond to any row in the projects table. Or user may delete a row in the projects table, leaving orphaned rows in the project_milestones table. This causes the application not to work properly.

The solution is to add an SQL FOREIGN KEY constraint to the project_milestones table to enforce the relationship between the projects and project_milestones tables.

You can create the FOREIGN KEY constraint when you create the table as follows:

CREATE TABLE project_milestones (
    milestone_id INT AUTO_INCREMENT PRIMARY KEY,
    project_id INT,
    milestone_name VARCHAR(100),
    FOREIGN KEY (project_id)
        REFERENCES projects (project_id)
);

The FOREIGN KEY clause promotes the project_id of the project_milestones table to become the foreign key that is referenced to the project_id of the projects table.

FOREIGN KEY (project_id)
        REFERENCES projects (project_id)

You can assign a name to a FOREIGN KEY constraint as follows:

CREATE TABLE project_milestones (
    milestone_id INT AUTO_INCREMENT PRIMARY KEY,
    project_id INT,
    milestone_name VARCHAR(100),
    CONSTRAINT fk_project FOREIGN KEY (project_id)
        REFERENCES projects (project_id)
);

fk_project is the name of the FOREIGN KEY constraint.

Adding FOREIGN KEY contraints to existing tables

To add a FOREIGN KEY constraint to existing table, you use the ALTER TABLE statement.

ALTER TABLE table_1
ADD CONSTRAINT fk_name FOREIGN KEY (fk_key_column)
   REFERENCES table_2(pk_key_column)

Suppose the project_milestones already exists without any predefined foreign key and you want to define a FOREIGN KEY constraint for the project_id column. To do so, you use the following ALTER TABLE statement:

ALTER TABLE project_milestones
ADD CONSTRAINT fk_project FOREIGN KEY(project_id)
   REFERENCES projects(project_id);

Removing foreign key constraints

To remove a foreign key constraint, you also use the ALTER TABLE statement as follows:

ALTER TABLE table_name
DROP CONSTRAINT fk_name;

If you are using MySQL, you can use a cleaner syntax as follows:

ALTER TABLE table_name
DROP FOREIGN KEY fk_name;

For example, to remove the fk_project foreign key constraint, you use the following statement:

ALTER TABLE project_milestones
DROP CONSTRAINT fk_project;