DDL

FOREIGN KEY

Q: Can FK columns be NULL?

Yes — by default, FK columns can be NULL. A NULL value isn't checked against the parent (it's "no relationship," not "broken relationship"). Add NOT NULL if every child must have a parent; allow NULL if the relationship is optional (e.g., coupon_id on orders that may not have a coupon).

Declare that a column in one table must match a primary key (or unique key) in another. The database refuses to insert a child row whose parent doesn't exist, and refuses to delete a parent row that still has children, unless you tell it what to do via ON DELETE / ON UPDATE actions.

Syntax #

sql

-- Inline, single column.
CREATE TABLE orders (
  id      INTEGER PRIMARY KEY,
  user_id INTEGER REFERENCES users(id)
);

-- Table-level, with a name and cascade rule.
CREATE TABLE orders (
  id      INTEGER PRIMARY KEY,
  user_id INTEGER,
  CONSTRAINT orders_user_fk
    FOREIGN KEY (user_id) REFERENCES users(id)
    ON DELETE CASCADE
    ON UPDATE NO ACTION
);

-- Add to an existing table.
ALTER TABLE orders
  ADD CONSTRAINT orders_user_fk
  FOREIGN KEY (user_id) REFERENCES users(id);

Example #

Loading SQL editor...

ON DELETE / ON UPDATE actions #

NO ACTION (default): refuse the parent delete/update if children exist. Same as RESTRICT on most engines.
CASCADE: propagate the delete or update to children. Powerful, and a common source of "where did all my rows go" stories. Use when children genuinely have no meaning without the parent.
SET NULL: keep children, set the FK column to NULL. The FK column must be nullable.
SET DEFAULT: set the FK column to its declared default. The default value must itself reference an existing parent row, or the constraint fails.

Dialect notes #

Snowflake / BigQuery / Redshift: foreign keys can be declared but are not enforced. They serve as documentation and as hints to the query planner. Your ETL or dbt tests have to enforce referential integrity.
MySQL: foreign keys only enforced on InnoDB tables, not MyISAM. Cascade actions work; SET DEFAULT is parsed but not enforced.
Postgres: full enforcement; DEFERRABLE INITIALLY DEFERRED lets you bulk-insert rows in any order within a transaction and have the engine check FKs at COMMIT.
SQLite: FK enforcement is off by default. Run PRAGMA foreign_keys = ON; per connection to turn it on.
DuckDB: FKs are enforced, but ON DELETE / ON UPDATE actions (CASCADE, SET NULL, SET DEFAULT) are not implemented, leaving only the default NO ACTION behavior. Delete children explicitly before the parent.
Indexing: most engines auto-index the parent's primary key but not the FK column on the child. Without an index on the child's FK column, parent deletes scan the whole child table: fine for small tables, painful for large ones. Add the index manually.

FAQ #

What does ON DELETE CASCADE do?

When a parent row is deleted, every child row referencing it is also deleted. Example: deleting a user with ON DELETE CASCADE on orders.user_id deletes all that user's orders too. Powerful — and a common cause of "where did all my rows go" stories. Use when children genuinely have no meaning without the parent.

Are foreign keys enforced on Snowflake / BigQuery / Redshift?

No — these warehouses accept FK declarations but don't enforce them. They serve as documentation and as hints to the query planner / BI tools. Enforcement falls to your ETL or dbt tests. Same applies to PRIMARY KEY and UNIQUE constraints on these engines.

Should I always add an index on the FK column?

Usually yes. Most engines auto-index the parent's primary key but not the FK column on the child. Without an index on the child's FK column, parent deletes scan the whole child table — fine for small tables, painful for large ones. MySQL InnoDB does auto-create an index on FK columns by default.

Can FK columns be NULL?

Yes — by default, FK columns can be NULL. A NULL value isn't checked against the parent (it's "no relationship," not "broken relationship"). Add NOT NULL if every child must have a parent; allow NULL if the relationship is optional (e.g., coupon_id on orders that may not have a coupon).

What's the difference between ON DELETE NO ACTION and RESTRICT?

On most engines they're effectively the same — both refuse the parent delete if children exist. Subtle difference in timing: RESTRICT checks immediately; NO ACTION may defer to end-of-transaction (Postgres). For everyday use, treat them as synonyms.

Can I temporarily disable FK checks for bulk loading?

Yes — useful when loading data in an order that would otherwise violate constraints. Postgres: SET CONSTRAINTS ALL DEFERRED inside a transaction. MySQL: SET FOREIGN_KEY_CHECKS = 0. Re-enable immediately after; leaving them off is a data integrity risk.

Warning

ON DELETE CASCADE is a sharp tool. DELETE FROM users WHERE id = 42 looks innocent until you realize it cascades through orders → order_items → shipments → reviews and removes thousands of rows in unrelated tables. For analytics-relevant tables, prefer ON DELETE NO ACTION (force callers to clean up explicitly) or soft deletes (is_deleted boolean) so the historical data survives. CASCADE is great for parent-of-detail relationships (an order and its line items), risky everywhere else.