Using CASCADE DELETE without understanding what gets deleted

Test CASCADE behavior in development, document what cascades, consider SET NULL for optional relationships

Developer adds ON DELETE CASCADE to all foreign keys. Later, deleting a customer accidentally deletes 10,000 orders and 50,000 order items. Data loss. Solution: Use CASCADE only for owned data (order items belong to orders). Use SET NULL for optional refs (manager_id). Use RESTRICT for protected data (can't delete customer with orders).

Critical

graph TD

A[DELETE customer] --> B{Has orders?}

B -->|CASCADE| C[Delete all orders]

C --> D[Delete all order items]

D --> E[10,000 orders deleted!]

B -->|RESTRICT| F[ERROR: Cannot delete]

F --> G[Data protected]

style E fill:#FFB6C6

style G fill:#90EE90

Forgetting to create indexes on foreign key columns

Always CREATE INDEX on foreign key columns immediately after creating the foreign key

Foreign key on orders.customer_id but no index. Deleting a customer requires full table scan of orders to check references. With 10M orders, DELETE takes 30 seconds. Solution: CREATE INDEX idx_orders_customer_id ON orders(customer_id). Now DELETE takes 50ms.

High

sequenceDiagram

participant App

participant Customers

participant Orders

App->>Customers: DELETE customer_id=123

Customers->>Orders: Check for references

Note over Orders: Without index: Full table scan (30s)

Note over Orders: With index: Index lookup (50ms)

Orders-->>Customers: No references found

Customers-->>App: DELETE success

Relying only on application validation without database constraints

Use both: application validation for UX, database constraints for integrity

Application checks email format but no UNIQUE constraint. Two users register with same email simultaneously. Both pass application validation. Both get inserted. Duplicate emails. Solution: Add UNIQUE constraint on email. Database rejects second insert even if application validation passes.

High

graph LR

A[User 1: Register] --> B[App Validation]

C[User 2: Register] --> D[App Validation]

B --> E[Both pass]

D --> E

E --> F{Database has UNIQUE?}

F -->|No| G[Both inserted - Duplicates!]

F -->|Yes| H[Second rejected - Integrity!]

style G fill:#FFB6C6