Previous

SQL FOREIGN KEY

A FOREIGN KEY is a constraint that establishes a relationship between two tables by linking a column in one table to the PRIMARY KEY of another table. It ensures referential integrity by preventing actions that would break the link between tables.

 

Key Features of SQL FOREIGN KEY

  • Ensures that values in the child table match existing values in the parent table.
  • Prevents deletion of a referenced record unless explicitly handled.
  • Helps maintain data integrity between related tables.
  • Can reference a single column or multiple columns (composite foreign key).

 

SQL FOREIGN KEY Syntax

Creating a FOREIGN KEY in Table Creation

CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    Name VARCHAR(100)
);

CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,
    EmployeeID INT,
    FOREIGN KEY (EmployeeID) REFERENCES Employees(EmployeeID)
);
  • The EmployeeID column in the Orders table is a foreign key referencing the EmployeeID column in the Employees table.

 

Adding a FOREIGN KEY to an Existing Table

ALTER TABLE Orders  
ADD CONSTRAINT FK_Employee  
FOREIGN KEY (EmployeeID) REFERENCES Employees(EmployeeID);

 

Removing a FOREIGN KEY

ALTER TABLE Orders  
DROP CONSTRAINT FK_Employee;

 

ON DELETE and ON UPDATE Actions

You can define how a FOREIGN KEY behaves when the referenced data changes.

Cascade Delete (Deletes related records)

CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,
    EmployeeID INT,
    FOREIGN KEY (EmployeeID) REFERENCES Employees(EmployeeID)  
    ON DELETE CASCADE
);
  • If an employee is deleted from Employees, all their related orders in Orders are also deleted.

Set NULL on Delete (Keeps records but sets foreign key to NULL)

CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,
    EmployeeID INT,
    FOREIGN KEY (EmployeeID) REFERENCES Employees(EmployeeID)  
    ON DELETE SET NULL
);
  • If an employee is deleted, the EmployeeID field in Orders is set to NULL.

 

Summary

  • A FOREIGN KEY links two tables by referencing the PRIMARY KEY of another table.
  • Ensures data integrity by preventing orphaned records.
  • Supports CASCADE and SET NULL actions for deletion and updates.
  • Can be added or removed using ALTER TABLE.
Previous