Format SQL with Foreign Key Constraints

Foreign key constraints are the mechanism relational databases use to enforce referential integrity — the guarantee that relationships between tables remain valid. Without foreign keys, it's possible to create orders referencing customer IDs that don't exist, or delete a product that still has pending order line items. With foreign keys, the database engine itself prevents these inconsistencies at the storage level, regardless of what the application code does. This example creates an orders table that references both a customers table and a products table. The foreign key on customer_id uses ON DELETE CASCADE — if a customer is deleted, all their orders are automatically deleted too. The foreign key on product_id uses ON DELETE RESTRICT — if you try to delete a product that still has orders referencing it, the database blocks the deletion entirely. These different behaviors reflect different business requirements. The three most important ON DELETE options: CASCADE propagates the deletion to child rows, maintaining referential integrity automatically at the cost of silently removing dependent data. RESTRICT (or NO ACTION) blocks the parent deletion if any child rows exist, forcing you to clean up children first — the safest option for preserving data. SET NULL nulls out the foreign key column in child rows, keeping them but removing the reference — useful for optional relationships like a category that products can exist without. Indexing foreign key columns: MySQL does not automatically create an index on foreign key columns (PostgreSQL does). Without an index, JOIN queries on the foreign key and ON DELETE operations require full table scans on the child table, which becomes catastrophically slow on large tables. Always add an explicit INDEX or let the foreign key constraint create one automatically (PostgreSQL behavior). Real-world schema design decisions: in a blog platform, should deleting a user cascade-delete all their posts (CASCADE) or orphan them as anonymous posts (SET NULL) or block user deletion until posts are reassigned (RESTRICT)? In an e-commerce system, should cancelling an order cascade-delete order line items (CASCADE) or keep them for audit purposes (RESTRICT with a separate soft-delete)? Tips: enable foreign key checks in MySQL explicitly — they're enabled by default but can be disabled for bulk imports (SET FOREIGN_KEY_CHECKS=0). Always re-enable them after the import and verify referential integrity. In PostgreSQL, use DEFERRABLE INITIALLY DEFERRED for foreign keys that need to be checked at transaction commit rather than per-statement.

CREATE TABLE orders (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  customer_id BIGINT UNSIGNED NOT NULL,
  product_id BIGINT UNSIGNED NOT NULL,
  quantity INT NOT NULL DEFAULT 1,
  total DECIMAL(10,2) NOT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE CASCADE,
  FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE RESTRICT
);