SQL REFERENCES

What is the SQL REFERENCES clause?

REFERENCES creates a foreign-key constraint linking columns in one table to primary or unique keys in another, enforcing relational integrity.

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 REFERENCES: PostgreSQL, MySQL, MariaDB, SQL Server, Oracle, SQLite, IBM Db2, Snowflake, Redshift

SQL REFERENCES Full Explanation

The REFERENCES clause is part of the SQL DDL and appears in CREATE TABLE and ALTER TABLE statements. It declares that the column or column list in the child table must match values that already exist in the referenced (parent) table’s primary key or unique key. When the constraint is active, the database automatically rejects INSERT, UPDATE, or DELETE operations that would break the relationship unless cascading actions are specified. A REFERENCES clause can be defined inline (right after the column) or as a separate table-level FOREIGN KEY constraint. Optional ON DELETE and ON UPDATE actions (CASCADE, SET NULL, SET DEFAULT, RESTRICT, NO ACTION) let you control what happens to child rows when parent rows change. Some dialects also allow MATCH options (SIMPLE, FULL) and deferrable constraints. Because REFERENCES depends on indexes in the parent table, the referenced columns must be declared PRIMARY KEY or UNIQUE. Failing to meet this requirement raises an error at table creation.

SQL REFERENCES Syntax

-- Inline column form
column_name data_type REFERENCES parent_table (parent_column)
    [ON DELETE action] [ON UPDATE action];

-- Table-level form
FOREIGN KEY (child_column[, ...])
    REFERENCES parent_table (parent_column[, ...])
    [ON DELETE action] [ON UPDATE action];

SQL REFERENCES Parameters

parent_table (identifier) - Name of the table being referenced.
parent_column(s) (identifier list) - Column or columns in the parent table that form a PRIMARY KEY or UNIQUE constraint.
action (keyword) - CASCADE | SET NULL | SET DEFAULT | RESTRICT | NO ACTION.
MATCH option (keyword, optional) - SIMPLE | FULL (supported in PostgreSQL, etc.).
DEFERRABLE settings (optional) - DEFERRABLE | NOT DEFERRABLE INITIALLY DEFERRED | INITIALLY IMMEDIATE.

Example Queries Using SQL REFERENCES

-- 1. Basic foreign key
CREATE TABLE departments (
  dept_id   INT PRIMARY KEY,
  dept_name TEXT NOT NULL
);

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

-- 2. Table-level definition with cascades
CREATE TABLE orders (
  order_id  INT PRIMARY KEY,
  customer_id INT,
  order_date DATE,
  FOREIGN KEY (customer_id)
    REFERENCES customers(id)
    ON DELETE CASCADE
    ON UPDATE NO ACTION
);

-- 3. Composite key reference
CREATE TABLE products (
  sku        TEXT,
  locale     TEXT,
  name       TEXT,
  PRIMARY KEY (sku, locale)
);

CREATE TABLE product_reviews (
  review_id  SERIAL PRIMARY KEY,
  sku        TEXT,
  locale     TEXT,
  rating     INT,
  FOREIGN KEY (sku, locale)
    REFERENCES products(sku, locale)
);

-- 4. Adding a reference later
ALTER TABLE employees
  ADD CONSTRAINT fk_emp_mgr
  FOREIGN KEY (manager_id)
  REFERENCES employees(emp_id);

Expected Output Using SQL REFERENCES

When these statements run, the database creates foreign-key constraints
Any subsequent attempts to insert or update a child row with a dept_id, customer_id, or (sku,locale) that does not exist in the parent table will fail
Deletes or updates on parent rows propagate or are blocked according to the specified ON DELETE/ON UPDATE actions

Use Cases with SQL REFERENCES

Enforce referential integrity between transactional tables (e.g., orders -> customers).
Model one-to-many or many-to-one relationships without relying solely on application logic.
Enable cascading deletes to keep child tables clean automatically.
Guarantee that lookup or dimension tables contain all referenced keys.

Common Mistakes with SQL REFERENCES

Referencing parent columns that are not PRIMARY KEY or UNIQUE.
Mismatching data types or collations between child and parent columns.
Forgetting to create indexes on foreign-key columns, leading to slow joins.
Assuming CASCADE is the default action (it is usually NO ACTION/RESTRICT).
Dropping parent rows without handling existing child references, causing constraint errors.

First Introduced In

SQL-92 standard

Frequently Asked Questions

What is the difference between inline and table-level REFERENCES?

Inline REFERENCES is placed right after a column definition and can reference a single column only. Table-level REFERENCES appears after all columns, lets you name the constraint, and supports multi-column keys.

Do I need an index on a foreign-key column?

Most databases automatically index primary keys but not foreign-key columns. Adding an index on the child column(s) greatly speeds up JOINs and DELETE/UPDATE checks.

What happens if I omit ON DELETE and ON UPDATE?

The default is usually NO ACTION (or RESTRICT). The database blocks parent deletes or key updates that would leave orphaned child rows.

Can I defer foreign-key checks until commit?

Yes, in databases that support DEFERRABLE constraints (PostgreSQL, Oracle). Declare the constraint DEFERRABLE INITIALLY DEFERRED and the check occurs at transaction commit instead of per statement.