Explain types of update anomalies in SQL with examples

Update Anomalies in SQL

Update anomalies are problems that arise when data is poorly structured, typically due to redundancy in a database. These issues occur when inserting, updating, or deleting data causes unintended consequences.

There are three main types of update anomalies:

1. Insertion Anomaly

Definition:

Occurs when you can’t insert data into the database without having other data.

Example:

Consider this table:

Now, you want to add a new course “AI” with no students enrolled yet.

But you can’t insert:

(NULL, NULL, 'AI', 'Dr. Sen')

This violates NOT NULL constraints — you’re forced to insert a fake student just to add a course.

2. Update Anomaly

Definition:

Occurs when changing data in one place requires multiple updates across the table to maintain consistency.

Example:

Same table:

Now, if Dr. Rao changes to “Dr. Ram”, you need to update all rows where he appears.
If you forget one, the data becomes inconsistent.

3. Deletion Anomaly

Definition:

Occurs when deleting a record removes unintended information.

Example:

If John drops the course and you delete this row, you lose all information about the ML course and Dr. Lee — even if others might join later.

How to Avoid These Anomalies?

• Use Normalization:
  • Separate data into multiple related tables.
  • Avoid redundancy.
• Example:
  • Create a Courses table and a Students table separately.
  • Use foreign keys to link them.

Summary Table