SQL Topics
FOREIGN KEY
title: FOREIGN KEY
A FOREIGN KEY links a column in one table to the primary key of another table.
This relationship prevents orphan records—rows that reference data that no longer exists—and keeps the database logically consistent.
What a Foreign Key Does
A foreign key ensures that every value inserted into the child table must already exist in the referenced parent table.
CREATE TABLE Departments (
DeptID INT PRIMARY KEY,
DeptName VARCHAR(100)
);
CREATE TABLE Employees (
EmpID INT PRIMARY KEY,
Name VARCHAR(100),
DeptID INT,
FOREIGN KEY (DeptID)
REFERENCES Departments(DeptID)
);Inserting an employee with DeptID = 999 when no such department exists will fail.
Defining a FOREIGN KEY
Inline form:
DeptID INT REFERENCES Departments(DeptID)Table constraint form:
CREATE TABLE Orders (
OrderID INT PRIMARY KEY,
CustomerID INT,
FOREIGN KEY (CustomerID)
REFERENCES Customers(CustomerID)
);Referential Actions
When the parent row changes or is deleted, the foreign key behavior is controlled by ON DELETE and ON UPDATE actions.
| Action | Behavior |
|---|---|
CASCADE | Deletes or updates child rows automatically |
SET NULL | Sets the foreign key to NULL |
RESTRICT | Prevents the parent deletion/update |
NO ACTION | Same as RESTRICT in most databases |
CREATE TABLE Orders (
OrderID INT PRIMARY KEY,
CustomerID INT,
FOREIGN KEY (CustomerID)
REFERENCES Customers(CustomerID)
ON DELETE CASCADE
ON UPDATE CASCADE
);Composite Foreign Key
A foreign key can reference multiple parent columns.
CREATE TABLE OrderItems (
OrderID INT,
ProductID INT,
Price DECIMAL(10,2),
FOREIGN KEY (OrderID, ProductID)
REFERENCES OrderedProducts(OrderID, ProductID)
);Real-World Uses
- Link orders to customers
- Connect enrollments to students and courses
- Relate invoices to clients and billing addresses
- Associate log entries with user accounts
Pseudocode example:
Common Mistakes
Missing indexes on foreign keys Adding an index to the foreign key column dramatically improves join performance and ON DELETE operations.
Ignoring referential actions Without ON DELETE, deleting a referenced parent row may fail when expected, or silently break relationships.
Frequently Asked Questions
Q: Can a foreign key reference a non-PRIMARY KEY? A: Yes, if the referenced column has a UNIQUE constraint.
Q: What is a self-referencing foreign key? A: A foreign key that points to the same table, commonly used for organizational hierarchies (Employees.ManagerID -> Employees.EmpID).
Q: How do I remove a foreign key? A: Use ALTER TABLE Orders DROP FOREIGN KEY fk_name;. Note that the exact syntax varies by database (PostgreSQL, MySQL, SQL Server).
Summary
- Foreign keys enforce relationships between tables.
- They prevent orphaned child records.
- Use
ON DELETEandON UPDATEto define behavior on parent changes. - Index foreign key columns for query performance.
Next Step
See UNIQUE to learn how to enforce distinct values, or return to the SQL Constraints overview.
Exam Focus
Revise definitions, diagrams, examples, and short-answer points for FOREIGN KEY.
Interview Use
Prepare one clear explanation, one practical example, and one common mistake for this SQL topic.
Search Terms
sql, sql complete guide, sql tutorial, sql notes, complete, guide, constraints, foreign
Related SQL Topics