What is the difference between ON DELETE CASCADE and RESTRICT?

CASCADE automatically deletes child rows when the parent is deleted. RESTRICT prevents deleting the parent if child rows exist, protecting against accidental data loss.

When should I use ON DELETE SET NULL?

Use SET NULL when child rows should remain but lose their reference, such as when a category is deleted but the products in it should stay uncategorized.

Do foreign keys slow down queries?

Foreign key checks add a small overhead to INSERT, UPDATE, and DELETE operations. The indexes on foreign key columns (which you should add manually in MySQL) actually speed up JOIN queries.

Format SQL with Foreign Key Constraints

Referential integrity through foreign keys prevents orphaned records and ensures data consistency. This example shows a normalized schema with multiple tables connected by foreign keys and different ON DELETE behaviors. Formatting makes the relationship between tables and the constraint actions explicit. Use this as a reference when designing relational schemas for new features.

Example

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
);

[ open in SQL Formatter → ]

FAQ

What is the difference between ON DELETE CASCADE and RESTRICT?: CASCADE automatically deletes child rows when the parent is deleted. RESTRICT prevents deleting the parent if child rows exist, protecting against accidental data loss.
When should I use ON DELETE SET NULL?: Use SET NULL when child rows should remain but lose their reference, such as when a category is deleted but the products in it should stay uncategorized.
Do foreign keys slow down queries?: Foreign key checks add a small overhead to INSERT, UPDATE, and DELETE operations. The indexes on foreign key columns (which you should add manually in MySQL) actually speed up JOIN queries.

Related Examples

Format a SQL CREATE TABLE Statement

CREATE TABLE statements define the schema of your database and should be readabl...

Format a SQL SELECT with JOIN

Multi-table JOIN queries quickly become hard to read when written on a single li...

Format a Batch SQL INSERT

Batch INSERT statements with multiple value rows are common in seed files, test ...