SQL FOREIGN

What is the SQL FOREIGN KEY constraint?

Defines a foreign key constraint that links columns in one table to primary or unique columns in another, enforcing referential 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 FOREIGN: PostgreSQL, MySQL (InnoDB, MariaDB), SQL Server, Oracle, SQLite (>=3.6.19), IBM Db2, Amazon Redshift (limited), Google BigQuery (DDL only)

SQL FOREIGN Full Explanation

The FOREIGN KEY constraint establishes a relationship between two tables by requiring that values in the child (referencing) column(s) match existing values in the parent (referenced) table’s primary or unique key column(s). When applied, the database checks every INSERT, UPDATE, or DELETE on the child or parent table to maintain referential integrity. Optional ON DELETE and ON UPDATE clauses let you specify actions such as CASCADE, RESTRICT, SET NULL, or NO ACTION when the parent row is modified. A child column must share the same data type and size as the referenced column, and the parent column must be indexed as a PRIMARY KEY or UNIQUE. Some engines (e.g., MyISAM in MySQL) ignore foreign keys, while others require explicit indexes. Deferrable constraints, supported in PostgreSQL and Oracle, let you delay enforcement until COMMIT. A foreign key can reference the same table (self-reference) or multiple columns (composite key).

SQL FOREIGN Syntax

-- inline in CREATE TABLE
column_name data_type REFERENCES parent_table(parent_column)

-- table-level in CREATE TABLE
FOREIGN KEY (child_column[, ...])
  REFERENCES parent_table(parent_column[, ...])
  [ON DELETE action]
  [ON UPDATE action]
  [DEFERRABLE | NOT DEFERRABLE]

-- ALTER TABLE
ALTER TABLE child_table
  ADD CONSTRAINT constraint_name
  FOREIGN KEY (child_column[, ...])
  REFERENCES parent_table(parent_column[, ...])
  [ON DELETE action] [ON UPDATE action];

SQL FOREIGN Parameters

child_column (s) - column list - Column(s) in the child table that hold the foreign key values.
parent_table (identifier) - Table that contains the referenced primary or unique key.
parent_column (s) - column list - Column(s) in the parent table being referenced.
action (keyword) - Optional referential action|||CASCADE, SET NULL, SET DEFAULT, RESTRICT, or NO ACTION.
DEFERRABLE (NOT DEFERRABLE) - keyword|||Optional clause to delay enforcement until commit (if supported).

Example Queries Using SQL FOREIGN

-- Example 1: Create tables with a foreign key
CREATE TABLE departments (
  id   INT PRIMARY KEY,
  name TEXT NOT NULL
);

CREATE TABLE employees (
  id             INT PRIMARY KEY,
  department_id  INT,
  FOREIGN KEY (department_id)
    REFERENCES departments(id)
    ON DELETE SET NULL
);

-- Example 2: Add a foreign key after table creation
ALTER TABLE orders
  ADD CONSTRAINT fk_customer
  FOREIGN KEY (customer_id)
  REFERENCES customers(id)
  ON DELETE CASCADE;

Expected Output Using SQL FOREIGN

Tables or constraints are created successfully
Subsequent attempts to insert a row in employees with a non-existing department_id, or delete a referenced departments
id, will fail or perform the specified ON DELETE action
In Example 2, deleting a customer row cascades to delete matching orders

Use Cases with SQL FOREIGN

Enforcing that every order references a valid customer.
Modeling one-to-many relationships such as blog_posts to authors.
Preventing orphaned rows when parent records are deleted.
Maintaining data integrity across microservice-owned tables in a shared database.

Common Mistakes with SQL FOREIGN

Mismatched data types or lengths between child and parent columns.
Forgetting to index the referenced parent column as PRIMARY KEY or UNIQUE.
Using ON DELETE CASCADE unintentionally and deleting large child datasets.
Attempting to reference a table in a different database without cross-database support.
Assuming all MySQL storage engines enforce foreign keys (MyISAM does not).

First Introduced In

SQL-92 standard

Frequently Asked Questions

What happens if I insert a child row with no matching parent?

The INSERT fails with an integrity constraint violation. The database rejects the row to protect referential integrity.

Can a table have multiple FOREIGN KEYs?

Yes. A table can define multiple foreign key constraints pointing to different parent tables or even to the same table.

How do I delete a parent row without affecting children?

Use ON DELETE RESTRICT or NO ACTION, or first update/delete the child rows explicitly, then remove the parent row.

Are FOREIGN KEY constraints expensive?

Minimal overhead when properly indexed. They often improve query plans because the optimizer knows the relationship between tables.