FOREIGN KEYDDL
Buy Me A Coffee

A column or set of columns that references the primary key of another table, enforcing referential integrity between parent and child tables.

A column (or set of columns) that references the primary key of another table, enforcing referential integrity. The foreign key table is the child; the referenced table is the parent. Prevents orphaned rows and enables JOINs. Disabled by some bulk-load operations.

Compatibility

Show:
Database System Support Status Since Version Notes
MySQL ✓ Supported all Foreign keys are only enforced by the InnoDB engine — MyISAM silently ignores FK constraints. ON DELETE / ON UPDATE actions: RESTRICT, CASCADE, SET NULL, NO ACTION, SET DEFAULT. Both columns must have compatible types and the parent column must be indexed.
PostgreSQL ✓ Supported all Full FK support with DEFERRABLE / INITIALLY DEFERRED options — you can defer constraint checking to end of transaction, useful for circular references or batch inserts. ON DELETE actions: NO ACTION, RESTRICT, CASCADE, SET NULL, SET DEFAULT.
SQL Server ✓ Supported all Standard FK support. ON DELETE / ON UPDATE: NO ACTION, CASCADE, SET NULL, SET DEFAULT. FKs can be temporarily disabled with ALTER TABLE ... NOCHECK CONSTRAINT for bulk loads. Cannot cascade to the same table through multiple paths (no multiple cascade paths).
Oracle ✓ Supported all Standard FK support. ON DELETE CASCADE and ON DELETE SET NULL supported; ON UPDATE CASCADE is NOT supported in Oracle. FKs can be disabled/enabled without dropping. An index on the FK column is recommended (Oracle does not auto-create it, unlike other engines).
SQLite ✓ Supported 3.6.19 Foreign key enforcement is DISABLED by default and must be enabled per connection with PRAGMA foreign_keys = ON. Without this pragma, FK constraints are parsed but silently ignored. This is a common gotcha for SQLite users.

Details

SQLite's foreign keys are off by default — the most common FK gotcha in the SQLite ecosystem. Oracle does not support ON UPDATE CASCADE. MySQL's FK enforcement depends on the storage engine (InnoDB yes, MyISAM no). PostgreSQL's DEFERRABLE constraint option is unique and powerful for complex batch operations.

Standard Syntax

CREATE TABLE orders ( id INT PRIMARY KEY, customer_id INT, FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE CASCADE ON UPDATE CASCADE );

Version Support

MySQL: Since all PostgreSQL: Since all SQL Server: Since all Oracle: Since all SQLite: Since 3.6.19

Per-Database Syntax & Notes

MySQL

Foreign keys are only enforced by the InnoDB engine — MyISAM silently ignores FK constraints. ON DELETE / ON UPDATE actions: RESTRICT, CASCADE, SET NULL, NO ACTION, SET DEFAULT. Both columns must have compatible types and the parent column must be indexed.

CREATE TABLE orders ( id INT AUTO_INCREMENT PRIMARY KEY, customer_id INT, FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE CASCADE ON UPDATE RESTRICT ) ENGINE=InnoDB; -- Disable FK checks for bulk loads: SET FOREIGN_KEY_CHECKS = 0;

PostgreSQL

Full FK support with DEFERRABLE / INITIALLY DEFERRED options — you can defer constraint checking to end of transaction, useful for circular references or batch inserts. ON DELETE actions: NO ACTION, RESTRICT, CASCADE, SET NULL, SET DEFAULT.

CREATE TABLE orders ( id SERIAL PRIMARY KEY, customer_id INT REFERENCES customers(id) ON DELETE CASCADE ); -- Deferrable constraint: ALTER TABLE orders ADD CONSTRAINT fk_customer FOREIGN KEY (customer_id) REFERENCES customers(id) DEFERRABLE INITIALLY DEFERRED;

SQL Server

Standard FK support. ON DELETE / ON UPDATE: NO ACTION, CASCADE, SET NULL, SET DEFAULT. FKs can be temporarily disabled with ALTER TABLE ... NOCHECK CONSTRAINT for bulk loads. Cannot cascade to the same table through multiple paths (no multiple cascade paths).

CREATE TABLE orders ( id INT IDENTITY PRIMARY KEY, customer_id INT, CONSTRAINT fk_orders_customer FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE CASCADE ); -- Disable for bulk load: ALTER TABLE orders NOCHECK CONSTRAINT fk_orders_customer;

Oracle

Standard FK support. ON DELETE CASCADE and ON DELETE SET NULL supported; ON UPDATE CASCADE is NOT supported in Oracle. FKs can be disabled/enabled without dropping. An index on the FK column is recommended (Oracle does not auto-create it, unlike other engines).

CREATE TABLE orders ( id NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY, customer_id NUMBER, CONSTRAINT fk_orders_customer FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE CASCADE ); -- Recommended: index the FK column manually CREATE INDEX idx_orders_cust ON orders(customer_id);

SQLite

Foreign key enforcement is DISABLED by default and must be enabled per connection with PRAGMA foreign_keys = ON. Without this pragma, FK constraints are parsed but silently ignored. This is a common gotcha for SQLite users.

-- REQUIRED: enable FK enforcement first PRAGMA foreign_keys = ON; CREATE TABLE orders ( id INTEGER PRIMARY KEY, customer_id INTEGER REFERENCES customers(id) ON DELETE CASCADE );