Adding a Foreign Key to an Existing SQL Table

1. Overview

In SQL, we can add a foreign key either during table creation by defining it in the CREATE TABLE statement or to an existing table using the ALTER TABLE statement.

In this tutorial, we’ll illustrate how to add a foreign key constraint to an existing SQL table. In our examples, we’ll use Baeldung’s simple University database.

2. Understanding Foreign Keys in SQL

A foreign key is a column or a group of columns in a table that references the primary key of another table.

It creates a parent-child relationship between two tables. Specifically, the parent table has the primary key, and the child table has the foreign key. This relationship ensures data integrity and consistency.

Adding a foreign key to an existing table ensures data integrity by linking it to another table. For example, linking a Student and Department table using a foreign key ensures that each department_id in the Student table must exist in the Department table. Moreover, this practice ensures all students are assigned to valid departments and maintains consistency across related SQL tables.

3. How to Add a Foreign Key to an Existing Table

We can add a foreign key to any existing SQL table. To do this, we use the ALTER TABLE statement with the ADD CONSTRAINT clause. The general syntax is as follows:

ALTER TABLE tableName
ADD CONSTRAINT foreignKeyName
FOREIGN KEY (columnName)
REFERENCES referencedTable (referencedColumn)
ON DELETE action
ON UPDATE action;

Here, foreignKeyName is optional and represents the name of the foreign key constraint. At the same time, referencedTable and referencedColumn are the parent tables and columns that must be specified and have compatible data types.

Meanwhile, the ON DELETE and ON UPDATE actions specify what to do when a referenced row is deleted or updated, respectively. The action can be CASCADE, SET NULL, NO ACTION, or RESTRICT.

In SQL Server, the syntax is similar, but instead of RESTRICT, we use NO ACTION to prevent actions when related rows exist.

4. Adding a Foreign Key Over a Single Column

Suppose we want to create a relationship between the Student and Department tables. For this purpose, we can add a foreign key constraint using the ADD CONSTRAINT clause. However, if the column already exists, we can skip creating a new column and jump straight into adding a foreign key constraint.

Let’s modify the Student table to include a new column department_id. This column references the id column from the Department table as a foreign key:

ALTER TABLE Student 
ADD COLUMN department_id INT;

In SQL Server, the ADD clause is used without the COLUMN keyword when adding a new column to an existing table:

ALTER TABLE Student
ADD department_id INT;

Now, let’s use the ALTER TABLE command to add a foreign key constraint on the department_id column in the Student table:

ALTER TABLE Student
ADD CONSTRAINT fk_student_department
FOREIGN KEY (department_id) REFERENCES Department(id);

This way, we can link the Student table with the Department table through the foreign key department_id.

This ALTER query works on PostgreSQL, MySQL, and SQL Server.

5. Adding a Foreign Key Over Multiple Columns

We can also add a foreign key to multiple columns of an existing table, which we specify as a comma-separated list.

5.1. PostgreSQL

In PostgreSQL, we can directly add a foreign key constraint on multiple columns without any additional requirements:

ALTER TABLE Course 
ADD CONSTRAINT fk_course_department 
FOREIGN KEY (department_id, name) REFERENCES Department (id, name);

In this query, we added a foreign key constraint fk_course_department to the department_id and name columns of the Course table. These columns reference the id and name columns of the Department table.

5.2. MySQL and SQL Server

In MySQL and SQL Server, adding a foreign key to multiple columns requires that the referenced columns have a UNIQUE constraint or COMPOSITE PRIMARY KEY.

For example, we alter the Department table to add a UNIQUE CONSTRAINT on the id and name columns:

ALTER TABLE Department
ADD CONSTRAINT uq_department UNIQUE (id, name);

After this, we can create a foreign key constraint in the Course table that references both the department_id and the name column from the Department table:

ALTER TABLE Course
ADD CONSTRAINT fk_course_department
FOREIGN KEY (department_id, name)
REFERENCES Department (id, name);

In this query, we added a foreign key constraint called fk_course_department in the Course table. It ensures that the combination of department_id and name in the Course table must match existing values in the id and name columns of the Department table.

6. Cost of Adding Foreign Keys to Existing Tables

Foreign keys are important for ensuring data integrity and creating relationships between tables.

However, they can slow down performance, add complexity, and cause possible downtime.

Moreover, if we define cascading actions, changes made in the parent table automatically update the related rows in the foreign key table. This operation ensures data integrity.

On the other hand, if we don’t manage this action carefully, it can lead to unintentional data loss.

For example, if ON DELETE CASCADE is enabled, deleting a department from the Departments table (the parent) will also delete all related student records from the Students table (the child). This cascade can lead to unintentional data loss if the user only intended to remove the department but not the linked student records.

To reduce these risks, first test such actions on a database’s copy and ensure regular backups are in place. This helps prevent accidental data loss and protects data integrity.

7. Conclusion

In this article, we discussed using the ALTER TABLE statement in PostgreSQL, MySQL, or SQL Server to add a foreign key constraint to an existing table.

Adding a foreign key to an existing table improves query performance by defining clear relationships between tables. While foreign keys enhance data integrity, their impact on performance and potential complexities must be considered.