SQL Reference ยท Constraints and Data Types
SQL FOREIGN KEY constraint
Reference for the SQL FOREIGN KEY constraint: enforce references between tables, REFERENCES syntax, NULL behavior, indexing and dialect differences.
A FOREIGN KEY constraint says that the values in a column (the child column) must match a value in another table's key column (the parent column), or be NULL. It stops you from creating an order for a customer that does not exist, or deleting a customer who still has orders.
The parent column must be a primary key or have a unique constraint. The foreign key is what turns two separate tables into a relationship you can trust, and it is the basis of every join in a normalized design.
CREATE TABLE orders ( id INTEGER PRIMARY KEY, customer_id INTEGER NOT NULL REFERENCES customers (id) );
Try it
Press Run (or Ctrl + Enter) to see the result here.
Press Run (or Ctrl + Enter) to see the result here.
Syntax
-- column constraint column_name data_type REFERENCES parent_table (parent_column) [ON DELETE action] [ON UPDATE action] -- table constraint [CONSTRAINT constraint_name] FOREIGN KEY (column_name [, ...]) REFERENCES parent_table (parent_column [, ...]) [ON DELETE action] [ON UPDATE action]
| Part | Description |
|---|---|
column_name |
The child column or columns holding the reference. |
parent_table (parent_column) |
The referenced key. If the column list is omitted, the parent's primary key is used. |
ON DELETE / ON UPDATE |
What happens to child rows when the parent row is deleted or its key changes: NO ACTION (the default), RESTRICT, CASCADE, SET NULL or SET DEFAULT. See ON DELETE and ON UPDATE. |
CONSTRAINT constraint_name |
Optional name for the constraint. |
Rules: a composite foreign key must have as many columns, of compatible types, as the key it references. If any column of a composite key is NULL, the standard's default MATCH SIMPLE treats the row as satisfying the constraint.
Dialect differences
| Dialect | Notes |
|---|---|
| SQLite | Declared but not enforced until PRAGMA foreign_keys = ON is run on each connection. ALTER TABLE cannot add a foreign key to an existing table. |
| PostgreSQL | Always enforced. Supports DEFERRABLE, NOT VALID (add without checking old rows) and MATCH FULL. |
| MySQL | Enforced by InnoDB only. MyISAM parses and ignores the clause. Column-level REFERENCES is parsed but ignored, so use the table form. |
| SQL Server | Enforced. ON DELETE CASCADE paths are restricted to avoid cycles ("multiple cascade paths"). |
| Oracle | Enforced. ON DELETE CASCADE and ON DELETE SET NULL exist, but there is no ON UPDATE clause. |
-- MySQL (table form is required) CREATE TABLE orders ( id BIGINT PRIMARY KEY, customer_id BIGINT NOT NULL, CONSTRAINT fk_orders_customer FOREIGN KEY (customer_id) REFERENCES customers (id) ); -- PostgreSQL: add later without scanning old rows, check them afterwards ALTER TABLE orders ADD CONSTRAINT fk_orders_customer FOREIGN KEY (customer_id) REFERENCES customers (id) NOT VALID; ALTER TABLE orders VALIDATE CONSTRAINT fk_orders_customer;
Usage notes
- The child can be
NULL. A nullable foreign key means "no parent", such as an employee without a manager. AddNOT NULLwhen a parent is mandatory. - Index the child column. Databases do not always create one for you. Without it, joins and parent deletes scan the child table. PostgreSQL and SQLite do not index foreign keys automatically, and InnoDB does.
- Order matters when loading data. Insert parents first, or defer the check with a deferrable constraint.
- Self references are allowed:
manager_idpointing atemployees (id)models a hierarchy. - Deleting the parent fails by default if children exist. Pick the action deliberately: cascading deletes are powerful and easy to regret.
- Cross-database references are not enforced in any of the major systems, and SQLite cannot reference a table in an attached database.
- Performance: each insert into the child looks up the parent key, which is cheap with the index the parent key already has.
Related
PRIMARY KEY,UNIQUE,ON DELETE and ON UPDATE,DEFERRABLE,PRAGMA- Lessons: Foreign keys, Relationships, Keys