Home / SQL / FOREIGN KEY

FOREIGN KEY

Overview

A FOREIGN KEY is a column or set of columns that references the PRIMARY KEY of another table. It establishes a relationship between two tables and maintains referential integrity.

Foreign keys ensure that values in one table must exist in another table, preventing orphaned records and maintaining data consistency across related tables.

Basic Syntax

FOREIGN KEY Syntax

CREATE TABLE table_name (
    column1 datatype,
    column2 datatype,
    FOREIGN KEY (column1) REFERENCES referenced_table(referenced_column)
);

Examples

Example 1: Basic Foreign Key

Link orders to customers

-- Parent table
CREATE TABLE Customers (
    customer_id INT PRIMARY KEY,
    name VARCHAR(100) NOT NULL
);

-- Child table with foreign key
CREATE TABLE Orders (
    order_id INT PRIMARY KEY,
    customer_id INT NOT NULL,
    order_date DATE,
    FOREIGN KEY (customer_id) REFERENCES Customers(customer_id)
);

Example 2: Named Foreign Key

Foreign key with constraint name

CREATE TABLE Orders (
    order_id INT PRIMARY KEY,
    customer_id INT NOT NULL,
    order_date DATE,
    CONSTRAINT fk_customer 
        FOREIGN KEY (customer_id) 
        REFERENCES Customers(customer_id)
);

Example 3: Multiple Foreign Keys

Table with multiple foreign keys

CREATE TABLE OrderItems (
    order_item_id INT PRIMARY KEY,
    order_id INT NOT NULL,
    product_id INT NOT NULL,
    quantity INT,
    FOREIGN KEY (order_id) REFERENCES Orders(order_id),
    FOREIGN KEY (product_id) REFERENCES Products(product_id)
);

Example 4: Composite Foreign Key

Foreign key on multiple columns

-- Parent table with composite primary key
CREATE TABLE StudentCourses (
    student_id INT,
    course_id INT,
    PRIMARY KEY (student_id, course_id)
);

-- Child table with composite foreign key
CREATE TABLE Grades (
    grade_id INT PRIMARY KEY,
    student_id INT,
    course_id INT,
    grade CHAR(1),
    FOREIGN KEY (student_id, course_id) 
        REFERENCES StudentCourses(student_id, course_id)
);

Referential Integrity Actions

Foreign keys can specify actions when referenced data is updated or deleted:

ON DELETE CASCADE

Deletes child records when parent is deleted:

Cascade delete

CREATE TABLE Orders (
    order_id INT PRIMARY KEY,
    customer_id INT NOT NULL,
    FOREIGN KEY (customer_id) 
        REFERENCES Customers(customer_id)
        ON DELETE CASCADE
);

-- When a customer is deleted, all their orders are deleted

ON DELETE SET NULL

Sets foreign key to NULL when parent is deleted:

Set NULL on delete

CREATE TABLE Orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    FOREIGN KEY (customer_id) 
        REFERENCES Customers(customer_id)
        ON DELETE SET NULL
);

ON UPDATE CASCADE

Updates foreign key when parent key is updated:

Cascade update

CREATE TABLE Orders (
    order_id INT PRIMARY KEY,
    customer_id INT NOT NULL,
    FOREIGN KEY (customer_id) 
        REFERENCES Customers(customer_id)
        ON UPDATE CASCADE
);

ON DELETE/UPDATE RESTRICT

Prevents deletion/update if child records exist (default behavior):

Restrict action

CREATE TABLE Orders (
    order_id INT PRIMARY KEY,
    customer_id INT NOT NULL,
    FOREIGN KEY (customer_id) 
        REFERENCES Customers(customer_id)
        ON DELETE RESTRICT
        ON UPDATE RESTRICT
);

Adding Foreign Key to Existing Table

Add foreign key constraint using ALTER TABLE:

Add foreign key later

ALTER TABLE Orders
ADD FOREIGN KEY (customer_id) 
    REFERENCES Customers(customer_id);

Dropping Foreign Key

Remove foreign key constraint:

Remove foreign key

-- MySQL
ALTER TABLE Orders
DROP FOREIGN KEY fk_customer;

-- SQL Server/PostgreSQL
ALTER TABLE Orders
DROP CONSTRAINT fk_customer;

Benefits of Foreign Keys

Referential integrity: Ensures valid relationships
Data consistency: Prevents orphaned records
Documentation: Shows relationships between tables
Automatic validation: Database enforces relationships
Cascade operations: Automatically handle related data

Best Practices

Always use foreign keys: For related tables
Name constraints: Use descriptive names for easier management
Choose appropriate actions: Consider CASCADE vs RESTRICT carefully
Index foreign keys: Improve JOIN performance
Reference primary keys: Always reference PKs or UNIQUE columns

Common Mistakes

Forgetting foreign keys: Leads to orphaned records
Wrong column reference: Must reference primary key or unique column
Data type mismatch: Foreign key and referenced column must match
Circular references: Avoid tables referencing each other

Next Steps

Learn about improving query performance with CREATE INDEX, or explore PRIMARY KEY constraints.

← Primary Key Create Index →