How to Add a Foreign Key in SQL to Link Tables

Add a Foreign Key

Write a SQL query to create a foreign key relationship between two tables.

Solution:

-- Create a new table "Departments" and link it to "Employees" using a foreign key.
CREATE TABLE Departments (
    DepartmentID INT PRIMARY KEY, -- Primary key for the "Departments" table.
    DepartmentName VARCHAR(50)    -- Column to store department names.
);
ALTER TABLE Employees
ADD DepartmentID INT, -- Add a column to reference the "Departments" table.
ADD CONSTRAINT FK_Department FOREIGN KEY (DepartmentID) REFERENCES Departments(DepartmentID);
-- Link Employees.DepartmentID to Departments.DepartmentID using a foreign key.

Explanation:

1. Purpose of the Query :

1. The goal is to establish a relationship between the Employees and Departments tables by creating a foreign key constraint.
2. This demonstrates how to use foreign keys to enforce referential integrity between related tables.

2. Key Components :

1. CREATE TABLE Departments : Creates a new table named Departments with a primary key column (DepartmentID) and a DepartmentName column.
2. ALTER TABLE Employees : Modifies the Employees table to include a new column (DepartmentID) that will reference the Departments table.
3. ADD CONSTRAINT FK_Department : Defines a foreign key constraint (FK_Department) that links the DepartmentID column in Employees to the DepartmentID column in Departments.

3. Why use a Foreign Key? :

1. A foreign key ensures that the values in the DepartmentID column of the Employees table must exist in the DepartmentID column of the Departments table.
2. This enforces referential integrity, ensuring that relationships between tables remain consistent and valid.

4. Real-World Application :

1. For example, in a company database, the Employees table might include a DepartmentID column to indicate which department each employee belongs to. The foreign key ensures that only valid department IDs can be assigned to employees.

Additional Notes:

• Foreign keys are essential for maintaining relationships between normalized tables.
• Scenarios where foreign keys are critical, such as:
• Ensuring that employees are assigned to valid departments.
• Supporting cascading updates or deletes to maintain consistency when related data changes.

Have another way to solve this solution? Contribute your code (and comments) through Disqus.

Previous SQL Exercise: How to Add a Unique Constraint to an SQL Column.
Next SQL Exercise: How to Drop a Table in SQL and Remove It Permanently.