A delete anomaly happens when removing one fact unintentionally removes another fact stored in the same record. It usually signals that one table is mixing information about different entities. section 3.3 section 1.3
A customer and product in one table
Imagine one table stores both Sci-Fi Collective customers and products. If a customer appears in only one row, deleting that customer's product row also deletes the customer's information. The database cannot remove one fact without losing the other. section 1.3
Prevent the problem by designing tables around separate entities, minimizing repeated data, and connecting related tables with keys. Normalization checks whether columns depend directly on the appropriate key; splitting a table can remove dependencies that create deletion anomalies. section 6.2 section 6.7
Keep these anomaly checks together:
- Insertion anomaly: adding one record requires unrelated data. section 3.3
- Update anomaly: modifying data creates inconsistencies. section 3.3
- Delete anomaly: removing data causes unintended information loss. section 3.3
