SQL Notes
How to use FOREIGN KEY constraints to enforce referential integrity between SQL tables, with examples of ON DELETE and ON UPDATE actions, and real-world relational patterns.
Databases are all about relationships. Customers place orders, students enroll in courses, employees belong to departments. The FOREIGN KEY constraint is how SQL enforces these relationships. It creates a link between two tables, ensuring that every reference points to a valid record.
Without foreign keys, nothing stops you from inserting an order for customer ID 999 when no such customer exists. That creates orphan records — data that references something that does not exist. Foreign keys prevent this entirely.
Foreign keys are the mechanism that makes a database truly relational. Without them, your tables are just independent collections of rows with no guaranteed connections between them. With foreign keys, the database itself enforces that relationships between tables are always valid. This is critically important in production systems where multiple applications, background jobs, and manual scripts all write to the same database. No matter how the data enters, foreign keys guarantee referential integrity is maintained.
Basic Syntax
CREATE TABLE Departments (
DeptID INT PRIMARY KEY,
DeptName VARCHAR(100) NOT NULL
);
CREATE TABLE Employees (
EmpID INT PRIMARY KEY,
Name VARCHAR(100) NOT NULL,
Email VARCHAR(255),
DeptID INT,
FOREIGN KEY (DeptID) REFERENCES Departments(DeptID)
);Now let us test the constraint:
-- First, add some departments
INSERT INTO Departments VALUES (1, 'Engineering');
INSERT INTO Departments VALUES (2, 'Marketing');
INSERT INTO Departments VALUES (3, 'HR');
-- Valid: DeptID 1 exists in Departments
INSERT INTO Employees VALUES (101, 'Rahul Sharma', 'rahul@company.com', 1);
-- Valid: DeptID 2 exists
INSERT INTO Employees VALUES (102, 'Priya Patel', 'priya@company.com', 2);
-- FAILS: DeptID 99 does not exist in Departments
INSERT INTO Employees VALUES (103, 'Amit Kumar', 'amit@company.com', 99);
-- ERROR: Cannot add or update a child row: foreign key constraint failsNamed Foreign Key Constraints
Always name your constraints for clarity:
CREATE TABLE Employees (
EmpID INT PRIMARY KEY,
Name VARCHAR(100) NOT NULL,
DeptID INT,
CONSTRAINT fk_emp_dept FOREIGN KEY (DeptID) REFERENCES Departments(DeptID)
);Named constraints produce clearer error messages and are easier to drop or modify later.
ON DELETE Actions
What happens when you try to delete a parent row that has child rows referencing it? By default, the database blocks the deletion. But you can configure different behaviors:
ON DELETE RESTRICT (Default)
Prevents deletion of the parent row if any child references it:
DELETE FROM Departments WHERE DeptID = 1;
-- ERROR: Cannot delete - employees reference this departmentON DELETE CASCADE
Automatically deletes all child rows when the parent is deleted:
CREATE TABLE Orders (
OrderID INT PRIMARY KEY,
CustomerID INT,
OrderDate DATE,
CONSTRAINT fk_order_customer
FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
ON DELETE CASCADE
);If you delete a customer, all their orders are automatically deleted too. Be careful with this — it can cause unintended data loss.
ON DELETE SET NULL
Sets the foreign key column to NULL when the parent is deleted:
CREATE TABLE Employees (
EmpID INT PRIMARY KEY,
Name VARCHAR(100) NOT NULL,
DeptID INT,
CONSTRAINT fk_emp_dept
FOREIGN KEY (DeptID) REFERENCES Departments(DeptID)
ON DELETE SET NULL
);If a department is deleted, employees in that department get NULL in their DeptID column instead of being deleted.
ON DELETE SET DEFAULT
Sets the foreign key to its DEFAULT value when the parent is deleted (supported in some databases):
CREATE TABLE Employees (
EmpID INT PRIMARY KEY,
Name VARCHAR(100) NOT NULL,
DeptID INT DEFAULT 0,
CONSTRAINT fk_emp_dept
FOREIGN KEY (DeptID) REFERENCES Departments(DeptID)
ON DELETE SET DEFAULT
);ON UPDATE Actions
Similar options exist for when a parent's primary key value is updated:
CREATE TABLE OrderItems (
ItemID INT PRIMARY KEY,
OrderID INT,
ProductID INT,
Quantity INT,
CONSTRAINT fk_item_order
FOREIGN KEY (OrderID) REFERENCES Orders(OrderID)
ON UPDATE CASCADE,
CONSTRAINT fk_item_product
FOREIGN KEY (ProductID) REFERENCES Products(ProductID)
ON UPDATE CASCADE
);With ON UPDATE CASCADE, if an OrderID changes in the Orders table, the change automatically propagates to OrderItems.
Real-World Example: E-Commerce Database
CREATE TABLE Customers (
CustomerID INT PRIMARY KEY AUTO_INCREMENT,
Name VARCHAR(100) NOT NULL,
Email VARCHAR(255) NOT NULL UNIQUE
);
CREATE TABLE Orders (
OrderID INT PRIMARY KEY AUTO_INCREMENT,
CustomerID INT NOT NULL,
OrderDate DATE DEFAULT CURRENT_DATE,
Status VARCHAR(20) DEFAULT 'Pending',
TotalAmount DECIMAL(10,2),
CONSTRAINT fk_order_customer
FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
ON DELETE RESTRICT
);
CREATE TABLE Products (
ProductID INT PRIMARY KEY AUTO_INCREMENT,
Name VARCHAR(200) NOT NULL,
Price DECIMAL(10,2) NOT NULL
);
CREATE TABLE OrderItems (
OrderID INT,
ProductID INT,
Quantity INT NOT NULL DEFAULT 1,
UnitPrice DECIMAL(10,2) NOT NULL,
PRIMARY KEY (OrderID, ProductID),
CONSTRAINT fk_oi_order
FOREIGN KEY (OrderID) REFERENCES Orders(OrderID)
ON DELETE CASCADE,
CONSTRAINT fk_oi_product
FOREIGN KEY (ProductID) REFERENCES Products(ProductID)
ON DELETE RESTRICT
);Notice the design decisions: deleting a customer is blocked if they have orders (RESTRICT), but deleting an order cascades to its items (CASCADE). You cannot delete a product if it appears in any order (RESTRICT).
Real-World Example: University System
CREATE TABLE Professors (
ProfID INT PRIMARY KEY,
Name VARCHAR(100) NOT NULL,
Department VARCHAR(50) NOT NULL
);
CREATE TABLE Courses (
CourseID VARCHAR(10) PRIMARY KEY,
CourseName VARCHAR(100) NOT NULL,
Credits INT NOT NULL,
ProfID INT,
CONSTRAINT fk_course_prof
FOREIGN KEY (ProfID) REFERENCES Professors(ProfID)
ON DELETE SET NULL
);If a professor leaves the university, their courses are not deleted — instead, ProfID becomes NULL, indicating the course needs a new instructor.
Adding a Foreign Key to an Existing Table
ALTER TABLE Employees
ADD CONSTRAINT fk_emp_dept
FOREIGN KEY (DeptID) REFERENCES Departments(DeptID);This only works if all existing DeptID values in Employees already exist in the Departments table.
Dropping a Foreign Key
MySQL
ALTER TABLE Employees DROP FOREIGN KEY fk_emp_dept;PostgreSQL and SQL Server
ALTER TABLE Employees DROP CONSTRAINT fk_emp_dept;Foreign Key and NULL Values
A foreign key column can be NULL unless you also specify NOT NULL. A NULL foreign key means "no relationship" — the employee is not assigned to any department yet:
-- Valid: Employee with no department
INSERT INTO Employees (EmpID, Name, DeptID)
VALUES (104, 'Sneha Verma', NULL);If you want every employee to have a department, add NOT NULL:
DeptID INT NOT NULL,
FOREIGN KEY (DeptID) REFERENCES Departments(DeptID)Common Mistakes to Avoid
Mistake 1: Inserting child rows before parent rows. Always insert parent table data first. You cannot reference a department that does not exist yet.
Mistake 2: Using CASCADE without understanding consequences. ON DELETE CASCADE can wipe out large amounts of data. Think carefully about whether you want deleting one customer to destroy all their order history.
Mistake 3: Creating circular references. Table A references Table B which references Table A. This creates insertion problems and should be avoided.
Mistake 4: Mismatched data types. The foreign key column must have the same data type as the referenced primary key column.
-- Wrong: INT referencing VARCHAR
DeptID INT REFERENCES Departments(DeptCode) -- if DeptCode is VARCHAR, this failsBest Practices
- Always name your foreign key constraints for easier debugging
- Choose ON DELETE actions carefully — RESTRICT is safest, CASCADE requires caution
- Index your foreign key columns for better JOIN performance
- Use NOT NULL when the relationship is mandatory
- Insert parent rows before child rows when loading data
- Consider SET NULL for optional relationships where the parent might be removed
Foreign keys are the backbone of relational database integrity. They ensure your data relationships always make sense, preventing the orphan records and broken references that plague databases without proper constraints.
Exam Focus
Revise definitions, diagrams, examples, and short-answer points for FOREIGN KEY Constraint in SQL.
Interview Use
Prepare one clear explanation, one practical example, and one common mistake for this SQL Complete Guide topic.
Search Terms
sql-complete-guide, sql complete guide, sql, complete, guide, constraints, foreign, key
Related SQL Complete Guide Topics