What is the difference between ON DELETE CASCADE and ON DELETE SET NULL?

ON DELETE CASCADE automatically deletes child rows when the parent row is deleted. ON DELETE SET NULL sets the foreign key column to NULL instead. Use CASCADE for dependent data (order items), SET NULL for optional relationships (user profiles).

Can I add a foreign key to a table that already has data?

Yes, but only if every existing value in the foreign key column exists in the referenced parent table. If there are orphan rows, the ALTER TABLE statement will fail. Clean your data first or use a NOT VALID / VALIDATE CONSTRAINT approach on PostgreSQL.

Does adding a foreign key lock the table?

Yes. Adding a foreign key constraint requires a full table scan to validate existing rows, which acquires an ACCESS EXCLUSIVE lock on PostgreSQL and similar locks on other databases. On large tables, consider adding the constraint as NOT VALID first, then running VALIDATE CONSTRAINT separately.

How to Add a Foreign Key in PostgreSQL — ALTER TABLE ADD FOREIGN KEY Examples

Q: How do I add a foreign key in PostgreSQL?

Use ALTER TABLE ... ADD CONSTRAINT ... FOREIGN KEY. Example: ALTER TABLE orders ADD CONSTRAINT fk_orders_user_id FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE;

🔗 Add a Foreign Key Constraint

Enforce referential integrity between two tables. Includes naming conventions and referential actions.

-- Basic foreign key
ALTER TABLE orders
  ADD CONSTRAINT fk_orders_user_id
  FOREIGN KEY (user_id) REFERENCES users(id);

-- With ON DELETE CASCADE
ALTER TABLE order_items
  ADD CONSTRAINT fk_order_items_order_id
  FOREIGN KEY (order_id) REFERENCES orders(id)
  ON DELETE CASCADE;

-- With ON DELETE SET NULL
ALTER TABLE comments
  ADD CONSTRAINT fk_comments_author_id
  FOREIGN KEY (author_id) REFERENCES users(id)
  ON DELETE SET NULL;

-- Large table: add without validation first, then validate
ALTER TABLE orders
  ADD CONSTRAINT fk_orders_user_id
  FOREIGN KEY (user_id) REFERENCES users(id)
  NOT VALID;

ALTER TABLE orders
  VALIDATE CONSTRAINT fk_orders_user_id;

-- Basic foreign key
ALTER TABLE orders
  ADD CONSTRAINT fk_orders_user_id
  FOREIGN KEY (user_id) REFERENCES users(id);

-- With ON DELETE CASCADE
ALTER TABLE order_items
  ADD CONSTRAINT fk_order_items_order_id
  FOREIGN KEY (order_id) REFERENCES orders(id)
  ON DELETE CASCADE;

-- With ON DELETE SET NULL
ALTER TABLE comments
  ADD CONSTRAINT fk_comments_author_id
  FOREIGN KEY (author_id) REFERENCES users(id)
  ON DELETE SET NULL;

-- InnoDB only supports CASCADE, SET NULL, RESTRICT, NO ACTION
-- MyISAM does not support foreign keys

-- SQLite supports foreign keys but they must be enabled:
PRAGMA foreign_keys = ON;

-- Add column with inline foreign key (SQLite 3.35.0+)
ALTER TABLE orders
  ADD COLUMN user_id INTEGER REFERENCES users(id);

-- For full constraint syntax, recreate the table:
CREATE TABLE orders_new (
  id INTEGER PRIMARY KEY,
  user_id INTEGER NOT NULL,
  total DECIMAL(10,2),
  FOREIGN KEY (user_id) REFERENCES users(id)
    ON DELETE CASCADE
);

INSERT INTO orders_new SELECT * FROM orders;
DROP TABLE orders;
ALTER TABLE orders_new RENAME TO orders;

-- Basic foreign key
ALTER TABLE orders
  ADD CONSTRAINT fk_orders_user_id
  FOREIGN KEY (user_id) REFERENCES users(id);

-- With ON DELETE CASCADE
ALTER TABLE order_items
  ADD CONSTRAINT fk_order_items_order_id
  FOREIGN KEY (order_id) REFERENCES orders(id)
  ON DELETE CASCADE;

-- With ON DELETE SET NULL
ALTER TABLE comments
  ADD CONSTRAINT fk_comments_author_id
  FOREIGN KEY (author_id) REFERENCES users(id)
  ON DELETE SET NULL;

-- Check existing data with NOCHECK (not recommended for production)
ALTER TABLE orders WITH NOCHECK
  ADD CONSTRAINT fk_orders_user_id
  FOREIGN KEY (user_id) REFERENCES users(id);

-- Basic foreign key
ALTER TABLE orders
  ADD CONSTRAINT fk_orders_user_id
  FOREIGN KEY (user_id) REFERENCES users(id);

-- With ON DELETE CASCADE
ALTER TABLE order_items
  ADD CONSTRAINT fk_order_items_order_id
  FOREIGN KEY (order_id) REFERENCES orders(id)
  ON DELETE CASCADE;

-- With ON DELETE SET NULL
ALTER TABLE comments
  ADD CONSTRAINT fk_comments_author_id
  FOREIGN KEY (author_id) REFERENCES users(id)
  ON DELETE SET NULL;

-- Oracle also supports ON DELETE SET DEFAULT

⚠️ Safety Note: Adding a foreign key on a large table can lock both tables while existing data is validated. On PostgreSQL, use NOT VALID + VALIDATE CONSTRAINT to split the operation. On SQL Server, consider adding during a maintenance window. Always ensure existing data satisfies the constraint before adding it.

💡 Naming Convention: Use fk_<child_table>_<column_name> for clarity. This makes it easy to identify which table and column a constraint applies to when reading error messages or query plans.

Spot missing constraints before they reach production. Compare Schemas →

How to Add a Foreign Key in PostgreSQL

What is a Foreign Key?

Basic Syntax

Referential Actions

🔗 Add a Foreign Key Constraint

Best Practices

Common Errors and Fixes

Related Migration Recipes

How to Add a Foreign Key in PostgreSQL

What is a Foreign Key?

Basic Syntax

Referential Actions

🔗 Add a Foreign Key Constraint

Best Practices

Common Errors and Fixes

Related Migration Recipes

⚡ Add an Index

🔒 Add a Unique Constraint

📖 All Migration Recipes