SQL FOREIGN KEY

What is a SQL FOREIGN KEY?

Defines a referential constraint that ensures values in one table match primary or unique key values in another table.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Compatible dialects for SQL FOREIGN KEY: PostgreSQL, MySQL, MariaDB, SQL Server, Oracle, SQLite (with limitations), Snowflake, Redshift, IBM Db2, and most ANSI-compliant systems

SQL FOREIGN KEY Full Explanation

A FOREIGN KEY constraint enforces referential integrity between two tables (or within the same table in a self-reference). The column or column group declared as a foreign key must contain values that already exist in the referenced table’s PRIMARY KEY or UNIQUE constraint. The database automatically prevents inserts, updates, or deletes that would break this relationship unless explicitly allowed through ON DELETE or ON UPDATE actions such as CASCADE, SET NULL, SET DEFAULT, or NO ACTION/RESTRICT. FOREIGN KEYs can be created inline within CREATE TABLE or added later with ALTER TABLE. They greatly simplify data consistency by delegating relationship checks to the database engine instead of application code. Caveats: every referenced column must be indexed (implicitly or explicitly); referenced and referencing columns must share compatible data types; and circular or multiple cascade paths are disallowed or limited in some systems.

SQL FOREIGN KEY Syntax

-- Inline in CREATE TABLE
CREATE TABLE child_table (
    child_id INT PRIMARY KEY,
    parent_id INT,
    FOREIGN KEY (parent_id)
        REFERENCES parent_table(parent_id)
        ON DELETE CASCADE
        ON UPDATE NO ACTION
);

-- Named constraint in CREATE TABLE
CREATE TABLE orders (
    id INT PRIMARY KEY,
    customer_id INT,
    CONSTRAINT fk_customer
        FOREIGN KEY (customer_id)
        REFERENCES customers(id)
);

-- Added later with ALTER TABLE
ALTER TABLE orders
    ADD CONSTRAINT fk_customer
    FOREIGN KEY (customer_id)
    REFERENCES customers(id);

SQL FOREIGN KEY Parameters

column_list - COLUMN One or more columns in the child table
reference_table TABLE - Table that holds the referenced key
ON DELETE action ENUM (CASCADE) - SET NULL|||SET DEFAULT|||RESTRICT|||NO ACTION (optional)
ON UPDATE action ENUM (CASCADE) - SET NULL|||SET DEFAULT|||RESTRICT|||NO ACTION (optional)

Example Queries Using SQL FOREIGN KEY

-- 1. Basic parent child relationship
CREATE TABLE departments (
    dept_id INT PRIMARY KEY,
    name TEXT NOT NULL
);

CREATE TABLE employees (
    emp_id  INT PRIMARY KEY,
    name    TEXT NOT NULL,
    dept_id INT,
    FOREIGN KEY (dept_id)
        REFERENCES departments(dept_id)
);

-- 2. Cascading deletes
CREATE TABLE comments (
    id        INT PRIMARY KEY,
    post_id   INT,
    body      TEXT,
    FOREIGN KEY (post_id)
        REFERENCES posts(id)
        ON DELETE CASCADE
);

-- 3. Add FK after table creation
ALTER TABLE invoices
    ADD CONSTRAINT fk_account
    FOREIGN KEY (account_id)
    REFERENCES accounts(id)
    ON UPDATE CASCADE;

Expected Output Using SQL FOREIGN KEY

The database creates the foreign key constraint
Subsequent INSERT or UPDATE statements on the child table will fail if the provided key does not exist in the parent table
ON DELETE/ON UPDATE rules dictate what happens when the referenced row changes or is removed (e
g
, child rows cascade delete, set to NULL, or block the operation)

Use Cases with SQL FOREIGN KEY

Enforcing that every order references a valid customer
Maintaining product-category relationships
Implementing cascading deletes of child records when a parent is removed
Guaranteeing data integrity across many-to-one and one-to-one relationships
Preventing orphaned rows during bulk imports or updates

Common Mistakes with SQL FOREIGN KEY

Referencing a column that is not PRIMARY KEY or UNIQUE in the parent table
Mismatched data types or lengths between parent and child columns
Forgetting to index foreign key columns, leading to poor performance
Expecting cascading actions that were not explicitly specified
Attempting multiple cascading paths on the same table (disallowed in some DBMSs)

First Introduced In

SQL-92 standard

Frequently Asked Questions

What happens if the parent row is deleted and I used ON DELETE CASCADE?

All matching child rows are automatically removed along with the parent, preventing orphaned data.

Can I disable a FOREIGN KEY temporarily?

Many databases allow disabling or deferring constraints within a transaction. Syntax varies (e.g., SET CONSTRAINTS ALL DEFERRED in PostgreSQL).

Are composite (multi-column) foreign keys allowed?

Yes. You can reference a composite PRIMARY KEY or UNIQUE index by listing columns in the same order on both sides of the constraint.

Does a FOREIGN KEY automatically create an index?

Some systems create it automatically (MySQL InnoDB, SQL Server). Others require you to create an index manually on the child column for performance.