The importance of atomicity and unique indexes in a SQL table

Always try to affect the maxiumum number of rows with the minimum number of transactions

For any SQL database to be trustworthy, it must protect both the integrity and the consistency of its data. That's where atomicity and unique indexes play a critical role. Atomicity means that a group of database operations succeeds or fails as a single unit. If one insert in a batch fails, the entire transaction is rolled back, preventing partial or inconsistent data from being stored.

This is especially important in ETL processes, financial systems, or any application where incomplete writes could cause downstream errors. Unique indexes, on the other hand, enforce business rules directly at the database level by preventing duplicate values in key columns.

Whether defined as a PRIMARY KEY or UNIQUE constraint, they guarantee that identifiers remain truly unique and that your data model cannot silently drift into inconsistency. Together, atomic transactions and unique indexes form a foundational safeguard; one that ensures changes are applied safely, and the other ensures only valid, non-duplicated data can exist in the first place.

Example

Let's say you want to create and populate a simple table containing the phonetic alphabet. Let's create a table to hold this information. In practice phonetic_id would be an auto increment column. Copy code 

  1. create table phonetic (
  2.     phonetic_id int not null primary key,
  3.     phonetic varchar(10) not null unique
  4. );

You could produce 26 insert statements like this: Copy code 

            
  1. insert phonetic(phonetic_id, phonetic) values (1, 'Alfa');
  2. insert phonetic(phonetic_id, phonetic) values (2, 'Bravo');
  3. insert phonetic(phonetic_id, phonetic) values (3, 'Charlie');
  4. insert phonetic(phonetic_id, phonetic) values (4, 'Delta');
  5. insert phonetic(phonetic_id, phonetic) values (5, 'Echo');
  6. insert phonetic(phonetic_id, phonetic) values (6, 'Foxtrot');
  7. insert phonetic(phonetic_id, phonetic) values (7, 'Golf');
  8. insert phonetic(phonetic_id, phonetic) values (8, 'Hotel');
  9. insert phonetic(phonetic_id, phonetic) values (9, 'India');
  10. insert phonetic(phonetic_id, phonetic) values (10, 'Juliet');
  11. insert phonetic(phonetic_id, phonetic) values (11, 'Kilo');
  12. insert phonetic(phonetic_id, phonetic) values (12, 'Lima');
  13. insert phonetic(phonetic_id, phonetic) values (13, 'Mike');
  14. insert phonetic(phonetic_id, phonetic) values (14, 'November');
  15. insert phonetic(phonetic_id, phonetic) values (15, 'Oscar');
  16. insert phonetic(phonetic_id, phonetic) values (16, 'Papa');
  17. insert phonetic(phonetic_id, phonetic) values (17, 'Quebec');
  18. insert phonetic(phonetic_id, phonetic) values (18, 'Romeo');
  19. insert phonetic(phonetic_id, phonetic) values (19, 'Sierra');
  20. insert phonetic(phonetic_id, phonetic) values (20, 'Tango');
  21. insert phonetic(phonetic_id, phonetic) values (21, 'Uniform');
  22. insert phonetic(phonetic_id, phonetic) values (22, 'Victor');
  23. insert phonetic(phonetic_id, phonetic) values (23, 'Whisky');
  24. insert phonetic(phonetic_id, phonetic) values (24, 'X-ray');
  25. insert phonetic(phonetic_id, phonetic) values (25, 'Yankee');
  26. insert phonetic(phonetic_id, phonetic) values (26, 'Zulu');

This is 26 separate transactions and take 0.383 secs to execute. If one of them fails the table is left in an inconsistent state. A better approach is to specify all values at once: Copy code 

            
  1. insert phonetic(phonetic_id, phonetic)
  2. values
  3.     (1, 'Alfa'),
  4.     (2, 'Bravo'),
  5.     (3, 'Charlie'),
  6.     (4, 'Delta'),
  7.     (5, 'Echo'),
  8.     (6, 'Foxtrot'),
  9.     (7, 'Golf'),
  10.     (8, 'Hotel'),
  11.     (9, 'India'),
  12.     (10, 'Juliet'),
  13.     (11, 'Kilo'),
  14.     (12, 'Lima'),
  15.     (13, 'Mike'),
  16.     (14, 'November'),
  17.     (15, 'Oscar'),
  18.     (16, 'Papa'),
  19.     (17, 'Quebec'),
  20.     (18, 'Romeo'),
  21.     (19, 'Sierra'),
  22.     (20, 'Tango'),
  23.     (21, 'Uniform'),
  24.     (22, 'Victor'),
  25.     (23, 'Whisky'),
  26.     (24, 'X-ray'),
  27.     (25, 'Yankee'),
  28.     (26, 'Zulu');

Took 0.026 sec, ten times faster than individual insert statements.

What would happen if I tried to insert a duplicate value? 

insert phonetic(phonetic_id, phonetic) values (100, 'Alfa');
Error Code: 1062. Duplicate entry 'Alfa' for key 'phonetic.phonetic'