FOREIGN KEY constraint in SQL

Introduction

A FOREIGN KEY constraint links two tables together. It makes sure that the data in one table matches data in another table, keeping information correct and connected.

When you want to connect customer orders to the customers who made them.

When you need to ensure that a product in an order exists in the product list.

When you want to prevent deleting a record that is still used in another table.

When you want to organize data into related tables to avoid repeating information.

Syntax

SQL

CREATE TABLE ChildTable (
  column1 datatype,
  column2 datatype,
  ...,
  FOREIGN KEY (column_name) REFERENCES ParentTable(parent_column)
);

The FOREIGN KEY column in the child table must match the data type of the referenced column in the parent table.

The referenced column in the parent table is usually a PRIMARY KEY or UNIQUE.

Examples

This creates an Orders table where CustomerID must match an existing CustomerID in the Customers table.

SQL

CREATE TABLE Orders (
  OrderID int PRIMARY KEY,
  CustomerID int,
  FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
);

This table links students and courses, ensuring both exist in their respective tables.

SQL

CREATE TABLE Enrollment (
  StudentID int,
  CourseID int,
  FOREIGN KEY (StudentID) REFERENCES Students(StudentID),
  FOREIGN KEY (CourseID) REFERENCES Courses(CourseID)
);

Sample Program

This example creates two tables: Departments and Employees. Employees have a DeptID that must exist in Departments. Then it inserts data and shows employee names with their department names.

SQL

CREATE TABLE Departments (
  DeptID int PRIMARY KEY,
  DeptName varchar(50)
);

CREATE TABLE Employees (
  EmpID int PRIMARY KEY,
  EmpName varchar(50),
  DeptID int,
  FOREIGN KEY (DeptID) REFERENCES Departments(DeptID)
);

INSERT INTO Departments VALUES (1, 'Sales');
INSERT INTO Departments VALUES (2, 'HR');

INSERT INTO Employees VALUES (101, 'Alice', 1);
INSERT INTO Employees VALUES (102, 'Bob', 2);

SELECT EmpName, DeptName FROM Employees
JOIN Departments ON Employees.DeptID = Departments.DeptID;

Important Notes

If you try to insert a value in the child table that does not exist in the parent table, the database will give an error.

Deleting a row in the parent table that is referenced by the child table can be blocked or cause changes depending on the FOREIGN KEY settings.

Summary

FOREIGN KEY connects two tables by matching columns.

It helps keep data accurate and related.

Use it to enforce rules about what data can be entered or deleted.