How FOREIGN KEY works in SQL? Best FOREIGN KEY examples

When working with relational databases, one of the most important concepts to understand is the FOREIGN KEY. It enforces referential integrity between tables, ensuring that the relationships between records remain consistent. In this article, I’ll explain how FOREIGN KEY works in SQL and provide the best FOREIGN KEY examples to help you build robust database schemas.

What Is a FOREIGN KEY in SQL?

A FOREIGN KEY is a column (or a set of columns) in one table that establishes a link to the PRIMARY KEY of another table. This constraint ensures that the value in the FOREIGN KEY column must always match an existing value in the referenced PRIMARY KEY column or be NULL.

The main purposes of a FOREIGN KEY are:

• Maintaining data integrity.
• Enforcing relationships between tables.
• Preventing orphaned rows.
• Enabling efficient joins and data retrieval.

How to Create a FOREIGN KEY in SQL?

You can define a FOREIGN KEY when creating a table or by adding it to an existing table. Here’s an example of defining a foreign key during table creation:

CREATE TABLE Customers (
    CustomerID INT PRIMARY KEY,
    Name VARCHAR(100) NOT NULL
);

CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,
    CustomerID INT,
    OrderDate DATE,
    FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
);

In this example:

• CustomerID in the Orders table references CustomerID in the Customers table.
• Each order must be associated with an existing customer.
• If CustomerID in Orders is not found in Customers, the database will prevent the insertion.

Adding a FOREIGN KEY to an Existing Table

If your database already contains tables and you need to add a FOREIGN KEY afterward, use the ALTER TABLE statement:

ALTER TABLE Orders
ADD CONSTRAINT fk_customer
FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID);

With this command, we add fk_customer as a FOREIGN KEY constraint after the table creation.

ON DELETE and ON UPDATE Actions

When defining a FOREIGN KEY, you can specify how changes in the parent table affect the child table using ON DELETE and ON UPDATE actions:

CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,
    CustomerID INT,
    OrderDate DATE,
    FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
    ON DELETE CASCADE
    ON UPDATE CASCADE
);

The available actions are:

Action	Description
CASCADE	Automatically updates or deletes related records.
SET NULL	Sets the FOREIGN KEY column to NULL when the referenced row is deleted or updated.
SET DEFAULT	Sets the column value to its default if the referenced row is deleted or updated.
RESTRICT	Prevents deletion or update if related records exist.
NO ACTION	Similar to RESTRICT, but handling is deferred.

Best FOREIGN KEY Examples in SQL

Example 1: Preventing Orphan Records

Imagine a database with employees and departments. Every employee must belong to an existing department.

CREATE TABLE Departments (
    DepartmentID INT PRIMARY KEY,
    DepartmentName VARCHAR(100) NOT NULL
);

CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    EmployeeName VARCHAR(100) NOT NULL,
    DepartmentID INT,
    FOREIGN KEY (DepartmentID) REFERENCES Departments(DepartmentID) 
    ON DELETE RESTRICT
);

With ON DELETE RESTRICT, you cannot delete a department if employees are still assigned to it.

Example 2: Handling Cascade Delete

Now let’s say when a customer is deleted, their orders should also be removed automatically.

CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,
    CustomerID INT,
    FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID) 
    ON DELETE CASCADE
);

With ON DELETE CASCADE, all orders from a deleted customer will also be deleted.

Common FOREIGN KEY Errors and How to Fix Them

When working with FOREIGN KEY constraints, you might encounter these common errors:

1. Cannot add or update a child row: a foreign key constraint fails
   Solution: Make sure the referenced PRIMARY KEY value exists before inserting or updating a FOREIGN KEY value.
2. Foreign key constraint is incorrectly formed
   Solution: Ensure that both the referenced column and the foreign key column have the same data type and length.
3. Integrity constraint violation: Cannot delete or update a parent row
   Solution: If you need to delete a referenced row, either remove child records first or use ON DELETE CASCADE.

Conclusion

Understanding how FOREIGN KEY works in SQL is crucial for maintaining data integrity and enforcing relationships between tables. Whether you’re designing a new database or improving an existing one, using FOREIGN KEYS correctly helps prevent invalid data and ensures consistency.

 

Other interesting article:

How INDEX works in SQL? Best INDEX examples