The Essential Guide To SQL Foreign Key Constraint (original) (raw)

Summary: In this tutorial, you’ll learn about the SQL foreign key and how to define a foreign key using the FOREIGN KEY constraint.

Introduction to SQL Foreign Key #

In SQL, a foreign key is a column or a set of columns in a table that references the primary key of another table.

A table with a foreign key is called a child or foreign key, or referencing table.
A table with primary key columns referenced by a foreign key is called a parent or reference table.

The primary purpose of a foreign key is to set up the relationship between the child and parent tables.

For example, for a value in a foreign key in the child table, you can always find the corresponding values in the primary key in the parent table.

Suppose we have two tables: projects and project_milestones.

The project_milestones table has a foreign key project_id that references the project_id in the projects table.
The project_milestones table is called a child or foreign key table, and the projects table is the parent or reference table.

For each row in the project_milestones table, you can find the project_id value in the projects table. This rule is called a referential integrity constraint between the two tables.

A table can have multiple foreign keys but only one primary key.

In SQL, you use a foreign key constraint to create a foreign key.

SQL Foreign Key Constraints #

Here’s the syntax for defining a foreign key constraint in the child table:

CONSTRAINT constraint_name FOREIGN KEY (column1, column2) REFERENCES parent_table(column1, column2) ON DELETE delete_action ON UPDATE update_action;Code language: SQL (Structured Query Language) (sql)

In this syntax:

First, specify the constraint name of the foreign key in the CONSTRAINT clause. Most database systems automatically assign a name if you don’t name the foreign key.
Second, provide one or more columns to include in the foreign key.-
Third, specify the parent table (reference table) with one or more columns.
Finally, define the action when the values in the parent table’s primary key are updated or deleted.

Defining a Foreign Key Constraint Example #

Step 1: Create a new table called projects to store project data:

CREATE TABLE projects ( project_id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY, project_name VARCHAR(255) NOT NULL, start_date DATE NOT NULL, end_date DATE NOT NULL );Code language: SQL (Structured Query Language) (sql)

Try it

Step 2: Create a new table called project_milestones to store project milestones:

CREATE TABLE project_milestones ( milestone_id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY, milestone VARCHAR(255), start_date DATE NOT NULL, end_date DATE NOT NULL, project_id INT NOT NULL, FOREIGN KEY (project_id) REFERENCES projects (project_id) );Code language: SQL (Structured Query Language) (sql)

Try it

In the project_milestones table, we define a foreign key constraint that includes the project_id and reference it to the project_id columns of the projects table.

Since we don’t explicitly specify the name of the foreign key constraint, the database system automatically generates one with the name project_milestones_project_id_fkey.

Note that the generated name may vary depending on the database system.

Inserting Data with Foreign Key Constraint #

Step 1: Insert a new row into the projects table:

INSERT INTO projects (project_name, start_date, end_date) VALUES ('Super App', '2025-01-01', '2025-12-31');Code language: SQL (Structured Query Language) (sql)

Try it

Step 2: Attempt to insert a new row into the project_milestones table with a project_id value that does not exist in the projects table:

INSERT INTO project_milestones (milestone, start_date, end_date, project_id) VALUES ('Initiation', '2025-01-01', '2025-01-31', 0);Code language: SQL (Structured Query Language) (sql)

Try it

The database system will issue an error message like this:

ERROR: insert or update on table "project_milestones" violates foreign key constraint "project_milestones_project_id_fkey" DETAIL: Key (project_id)=(0) is not present in table "projects".Code language: SQL (Structured Query Language) (sql)

The error message indicated that the project_id 0 is not present in the projects table.

Step 3: Insert a new row into the project_milestones table with a valid project_id value

INSERT INTO project_milestones (milestone, start_date, end_date, project_id) VALUES ('Initiation', '2025-01-01', '2025-01-31', 1);Code language: SQL (Structured Query Language) (sql)

Try it

Adding FOREIGN KEY Constraints to Existing Tables #

To add a foreign key constraint to a table, you use the ALTER TABLE ... ADD CONSTRAINT statement:

ALTER TABLE foreign_key_table ADD CONSTRAINT constraint_name FOREIGN KEY (column1, column2) REFERENCES parent_table(column1, column2) ON UPDATE update_action ON DELETE delete_action;Code language: SQL (Structured Query Language) (sql)

If the tables have data, you must ensure that it is valid before adding a foreign key constraint to the foreign key table. Otherwise, you’ll encounter a constraint violation error.

Step 1: Create a table calledproject_tasks :

CREATE TABLE project_tasks ( task_id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY, title VARCHAR(255) NOT NULL, completed BOOL NOT NULL DEFAULT FALSE, start_date DATE NOT NULL, end_date DATE NOT NULL, milestone_id INT );Code language: SQL (Structured Query Language) (sql)

Try it

Step 2: Add a foreign key constraint to the project_tasks table:

ALTER TABLE project_tasks ADD CONSTRAINT project_tasks_milestone_id_fkey FOREIGN KEY (milestone_id) REFERENCES project_milestones (milestone_id);Code language: SQL (Structured Query Language) (sql)

Try it

Dropping Foreign Key Constraints #

To drop a foreign key constraint, you use the ALTER TABLE statement:

ALTER TABLE foreign_key_table DROP CONSTRAINT fk_name;Code language: SQL (Structured Query Language) (sql)

For example, the following statement removes the project_tasks_milestone_id_fkey foreign key constraint:

ALTER TABLE project_tasks DROP CONSTRAINT project_tasks_milestone_id_fkey;Code language: SQL (Structured Query Language) (sql)

Try it

Delete Actions #

When you delete a row in the parent table, the database system needs to decide what to do with the row in the child table.

For example, if you delete a row from the projects table, the database system must know what to do with the rows whose project_id is related to the deleted project.

By default, the database system does not allow you to delete a row in the parent table if its related rows in the child table exist. If you attempt to do so, the database system rejects the deletion and issues an error.

SQL Deletion Actions for a Foreign Key:

RESTRICT: You cannot delete a row in the parent table when a row in the child table references the value in the parent table.
SET NULL: When you delete a related row from the parent table, the database system sets the values in the foreign key columns to NULL.
SET DEFAULT: When you delete a related row from the parent table, the database system updates the foreign key columns with their default values.
CASCADE: The database system deletes the related rows in the child table when you delete a related row from the parent table.

Update Actions #

The update action instructs the database system to handle the rows in the child table when you update related rows in the parent table.

SQL defines the following deletion action for a foreign key:

RESTRICT: The database system prevents updating the primary key if dependent rows exist in the child table. This is the default behavior.
SET NULL: When you update the primary key values of the parent table, the database system sets the values in the foreign key columns of the related rows to NULL.
SET DEFAULT: When you update the primary key values of the parent table, the database system sets the values in the foreign key columns of the related rows to their default values.
CASCADE: When you update the primary key values of the parent table, the database system automatically updates the values in the foreign key columns.

In practice, you rarely update the primary key values. Therefore, you rarely need the ON UPDATE action.

Summary #

Use the SQL foreign key constraint to enforce referential integrity between two tables.

Quiz #

Databases #

Was this tutorial helpful ?