SQL - FOREIGN KEY Constraint | Application Architect

Key Insights

Foreign keys enforce referential integrity by ensuring child table records reference valid parent table records, preventing orphaned data and maintaining database consistency
Cascading actions (CASCADE, SET NULL, SET DEFAULT) automate related record updates or deletions, eliminating manual cleanup code and reducing application complexity
Composite foreign keys and self-referencing relationships handle complex data models, while proper indexing on foreign key columns prevents performance degradation in large datasets

Understanding Foreign Key Constraints

A foreign key constraint establishes a link between two tables by ensuring that values in one table’s column(s) match values in another table’s primary key or unique constraint. This relationship enforces referential integrity at the database level rather than relying on application logic.

CREATE TABLE departments (
    department_id INT PRIMARY KEY,
    department_name VARCHAR(100) NOT NULL
);

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    department_id INT,
    CONSTRAINT fk_employee_department 
        FOREIGN KEY (department_id) 
        REFERENCES departments(department_id)
);

In this example, employees.department_id references departments.department_id. Any attempt to insert an employee with a non-existent department ID will fail:

INSERT INTO departments (department_id, department_name) 
VALUES (1, 'Engineering'), (2, 'Sales');

-- This succeeds
INSERT INTO employees (employee_id, first_name, last_name, department_id)
VALUES (101, 'John', 'Doe', 1);

-- This fails - department_id 99 doesn't exist
INSERT INTO employees (employee_id, first_name, last_name, department_id)
VALUES (102, 'Jane', 'Smith', 99);
-- Error: Foreign key constraint violation

Cascading Actions

Cascading actions define what happens to child records when parent records are updated or deleted. Without cascading rules, you cannot delete a parent record if child records reference it.

CASCADE

The CASCADE option automatically propagates changes to child records:

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    order_date DATE,
    CONSTRAINT fk_orders_customer 
        FOREIGN KEY (customer_id) 
        REFERENCES customers(customer_id)
        ON DELETE CASCADE
        ON UPDATE CASCADE
);

CREATE TABLE order_items (
    item_id INT PRIMARY KEY,
    order_id INT,
    product_id INT,
    quantity INT,
    CONSTRAINT fk_items_order 
        FOREIGN KEY (order_id) 
        REFERENCES orders(order_id)
        ON DELETE CASCADE
);

When you delete a customer, all their orders are automatically deleted. When those orders are deleted, all order items are also deleted:

-- Deletes customer, all their orders, and all items in those orders
DELETE FROM customers WHERE customer_id = 1001;

SET NULL

SET NULL replaces foreign key values with NULL when the parent record is deleted:

CREATE TABLE projects (
    project_id INT PRIMARY KEY,
    project_name VARCHAR(100),
    manager_id INT,
    CONSTRAINT fk_project_manager 
        FOREIGN KEY (manager_id) 
        REFERENCES employees(employee_id)
        ON DELETE SET NULL
);

If a manager leaves the company, their projects remain but the manager_id becomes NULL:

DELETE FROM employees WHERE employee_id = 5;
-- All projects previously managed by employee 5 now have manager_id = NULL

SET DEFAULT

SET DEFAULT assigns a default value when the parent is deleted (supported in PostgreSQL and some other databases):

CREATE TABLE tasks (
    task_id INT PRIMARY KEY,
    task_name VARCHAR(200),
    assigned_to INT DEFAULT 1,
    CONSTRAINT fk_task_employee 
        FOREIGN KEY (assigned_to) 
        REFERENCES employees(employee_id)
        ON DELETE SET DEFAULT
);

RESTRICT and NO ACTION

RESTRICT and NO ACTION prevent deletion of parent records if child records exist:

CREATE TABLE categories (
    category_id INT PRIMARY KEY,
    category_name VARCHAR(100)
);

CREATE TABLE products (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(100),
    category_id INT,
    CONSTRAINT fk_product_category 
        FOREIGN KEY (category_id) 
        REFERENCES categories(category_id)
        ON DELETE RESTRICT
);

-- This fails if any products exist in category 5
DELETE FROM categories WHERE category_id = 5;

Composite Foreign Keys

Composite foreign keys reference multiple columns, useful when the parent table has a composite primary key:

CREATE TABLE courses (
    department_code VARCHAR(10),
    course_number VARCHAR(10),
    course_title VARCHAR(200),
    PRIMARY KEY (department_code, course_number)
);

CREATE TABLE course_sections (
    section_id INT PRIMARY KEY,
    department_code VARCHAR(10),
    course_number VARCHAR(10),
    semester VARCHAR(20),
    instructor VARCHAR(100),
    CONSTRAINT fk_section_course 
        FOREIGN KEY (department_code, course_number)
        REFERENCES courses(department_code, course_number)
);

All columns in the composite foreign key must match the parent table’s composite key:

INSERT INTO courses VALUES ('CS', '101', 'Introduction to Programming');

-- This succeeds
INSERT INTO course_sections VALUES (1, 'CS', '101', 'Fall 2024', 'Dr. Smith');

-- This fails - no course CS-102
INSERT INTO course_sections VALUES (2, 'CS', '102', 'Fall 2024', 'Dr. Jones');

Self-Referencing Foreign Keys

Self-referencing foreign keys create hierarchical relationships within a single table:

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    manager_id INT,
    CONSTRAINT fk_employee_manager 
        FOREIGN KEY (manager_id) 
        REFERENCES employees(employee_id)
        ON DELETE SET NULL
);

-- CEO has no manager
INSERT INTO employees VALUES (1, 'Sarah', 'Johnson', NULL);

-- VP reports to CEO
INSERT INTO employees VALUES (2, 'Michael', 'Chen', 1);

-- Manager reports to VP
INSERT INTO employees VALUES (3, 'Emily', 'Rodriguez', 2);

-- Query organizational hierarchy
SELECT 
    e.employee_id,
    e.first_name || ' ' || e.last_name AS employee,
    m.first_name || ' ' || m.last_name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.employee_id;

Adding and Dropping Foreign Keys

Add foreign keys to existing tables using ALTER TABLE:

-- Add foreign key
ALTER TABLE employees
ADD CONSTRAINT fk_employee_department
FOREIGN KEY (department_id)
REFERENCES departments(department_id);

-- Drop foreign key
ALTER TABLE employees
DROP CONSTRAINT fk_employee_department;

Before adding a foreign key, ensure existing data satisfies the constraint:

-- Check for orphaned records
SELECT DISTINCT e.department_id
FROM employees e
LEFT JOIN departments d ON e.department_id = d.department_id
WHERE e.department_id IS NOT NULL 
  AND d.department_id IS NULL;

Performance Considerations

Foreign keys impact performance in two ways: constraint checking during DML operations and join performance during queries.

Indexing Foreign Key Columns

Always create indexes on foreign key columns to optimize joins and prevent lock escalation:

CREATE INDEX idx_employees_department 
ON employees(department_id);

CREATE INDEX idx_order_items_order 
ON order_items(order_id);

Without indexes, deleting a parent record requires a full table scan of child tables to check for references.

Bulk Operations

For bulk data loads, temporarily disable foreign key checks (use cautiously):

-- MySQL
SET FOREIGN_KEY_CHECKS = 0;
-- Bulk insert operations
SET FOREIGN_KEY_CHECKS = 1;

-- PostgreSQL
ALTER TABLE employees DISABLE TRIGGER ALL;
-- Bulk insert operations
ALTER TABLE employees ENABLE TRIGGER ALL;

Checking Foreign Key Constraints

Query system catalogs to inspect foreign key definitions:

-- PostgreSQL
SELECT
    tc.table_name,
    kcu.column_name,
    ccu.table_name AS foreign_table_name,
    ccu.column_name AS foreign_column_name,
    rc.update_rule,
    rc.delete_rule
FROM information_schema.table_constraints tc
JOIN information_schema.key_column_usage kcu
    ON tc.constraint_name = kcu.constraint_name
JOIN information_schema.referential_constraints rc
    ON tc.constraint_name = rc.constraint_name
JOIN information_schema.constraint_column_usage ccu
    ON rc.constraint_name = ccu.constraint_name
WHERE tc.constraint_type = 'FOREIGN KEY'
  AND tc.table_name = 'employees';

Foreign keys are fundamental to maintaining data integrity in relational databases. They prevent invalid data at the database level, reduce application code complexity, and make relationships explicit in the schema. Proper use of cascading actions and indexing ensures both data consistency and optimal performance.