SQL guide · Constraints

SQL FOREIGN KEY Constraint: Link Tables Safely

A FOREIGN KEY constraint makes a column in one table point at the primary key, or another unique key, of a second table. The database then refuses any value in the child column that does not exist in the parent table. If orders.customer_id is a foreign key to customers.customer_id, you cannot create an order for a customer that does not exist, and you cannot silently delete a customer who still has orders.

This guarantee is called referential integrity, and it prevents orphan rows: records that refer to something that no longer exists. Without it, bad data creeps in through application bugs, manual fixes and partial imports, and reports start to show totals that do not add up. Foreign keys also document the shape of the schema, which helps analysts and tools understand which tables join to which.

You control what happens when a parent row is changed or removed using referential actions such as ON DELETE CASCADE, ON DELETE SET NULL or the default behaviour of blocking the change. A foreign key column may hold NULL unless you also add NOT NULL, which is how optional relationships are modelled. Note that most databases do not automatically index the child column, so add an index when you join or delete by it often.

Basic syntax

CREATE TABLE child_table (
  child_id   data_type PRIMARY KEY,
  parent_id  data_type,
  CONSTRAINT fk_name FOREIGN KEY (parent_id)
    REFERENCES parent_table (parent_id)
    [ON DELETE action]
    [ON UPDATE action]
);

ALTER TABLE child_table
ADD CONSTRAINT fk_name FOREIGN KEY (parent_id)
  REFERENCES parent_table (parent_id);

parent_table (parent_id) must be a primary key or unique column set, and the data types must be compatible. action is one of NO ACTION, RESTRICT, CASCADE, SET NULL or SET DEFAULT, although not every database supports every option.

SQL FOREIGN KEY examples

Foreign key inside CREATE TABLE

Each order must belong to an existing customer. Because customer_id allows nulls here, an order without a customer is still permitted; add NOT NULL if every order needs one. Inserting an order with an unknown customer id raises a constraint violation.

CREATE TABLE customers (
  customer_id INT PRIMARY KEY,
  full_name   VARCHAR(100) NOT NULL
);

CREATE TABLE orders (
  order_id    INT PRIMARY KEY,
  customer_id INT NOT NULL,
  order_date  DATE NOT NULL,
  CONSTRAINT fk_orders_customer FOREIGN KEY (customer_id)
    REFERENCES customers (customer_id)
);

Cascade deletes to child rows

With ON DELETE CASCADE, deleting an order also deletes its lines, so you never leave orphans behind. Use it for true parent and child data where the child has no meaning alone. Be careful with it on large tables, because a single delete can remove many rows.

CREATE TABLE order_lines (
  line_id    INT PRIMARY KEY,
  order_id   INT NOT NULL,
  product_id INT NOT NULL,
  quantity   INT NOT NULL,
  FOREIGN KEY (order_id) REFERENCES orders (order_id)
    ON DELETE CASCADE
);

DELETE FROM orders WHERE order_id = 1001;
-- all order_lines rows for order 1001 are removed too

Keep the child row but clear the link

When the relationship is optional, ON DELETE SET NULL keeps the child row and blanks the reference. An employee whose manager leaves stays in the table with no manager assigned. The foreign key column must allow nulls for this to work.

CREATE TABLE employees (
  employee_id INT PRIMARY KEY,
  name        VARCHAR(100) NOT NULL,
  manager_id  INT NULL,
  CONSTRAINT fk_employees_manager FOREIGN KEY (manager_id)
    REFERENCES employees (employee_id)
    ON DELETE SET NULL
);

Add a foreign key to an existing table

Use ALTER TABLE when the table already exists. Every existing value in the child column must already match a parent row or be null, otherwise the statement fails. Check for orphans first with a left join.

-- Find orphan rows first
SELECT o.order_id
FROM orders o
LEFT JOIN customers c ON c.customer_id = o.customer_id
WHERE c.customer_id IS NULL;

ALTER TABLE orders
ADD CONSTRAINT fk_orders_customer FOREIGN KEY (customer_id)
  REFERENCES customers (customer_id);

Where SQL dialects differ

PostgreSQL: Supports all five referential actions plus DEFERRABLE INITIALLY DEFERRED, which delays the check until commit. ALTER TABLE ... ADD CONSTRAINT ... NOT VALID adds the key without scanning existing rows, then VALIDATE CONSTRAINT checks them later with a lighter lock.

MySQL: Foreign keys work only in InnoDB tables; MyISAM parses and ignores them. MySQL creates an index on the child column automatically if none exists. Drop with ALTER TABLE t DROP FOREIGN KEY fk_name, not DROP CONSTRAINT, in older versions (8.0.19 and later accept both).

SQL Server: Cascading paths are restricted: you cannot create a foreign key that would produce multiple cascade paths or cycles, and self-referencing keys cannot cascade. NO ACTION is the default. Use WITH NOCHECK to skip validation of existing rows, but the key is then marked untrusted.

SQLite: Foreign key enforcement is off by default for each connection; run PRAGMA foreign_keys = ON to enable it. You can only declare a foreign key when creating the table, because ALTER TABLE ADD CONSTRAINT is not supported.

Oracle: Supports ON DELETE CASCADE and ON DELETE SET NULL but has no ON UPDATE clause. Parent keys are normally never updated in Oracle designs. Constraints can be created DISABLE or ENABLE NOVALIDATE for data loads.

Common mistakes

  • Referencing a column that is not a primary key or unique key. The parent column must be uniquely constrained or the definition is rejected.
  • Mismatched data types between parent and child, such as INT against BIGINT or different string collations. Some engines reject this and others cast silently and slow joins down.
  • Using ON DELETE CASCADE everywhere without thinking. A careless delete on a top-level table can wipe out huge amounts of related data.
  • Not indexing the foreign key column. Deletes and updates on the parent must scan the child table, which becomes very slow as it grows.
  • Loading data in the wrong order. Insert parent rows before child rows, or defer the constraint where the database supports it.

Related SQL guides

Browse all SQL guides

Need help writing the SQL?

Describe your tables and the result you want, then review the generated query against your database before running it.

Try the AI SQL generator