Why auto_incremnt column increment it value after insert statement failed

Question

I am using MySQL. I create two new tables. After inserting a new row in Employees table , i deliberately try to insert a new row in Orders table whose reference not exist in Employees table(i know it'll produce error). After that when i try to insert a new row in Orders table whose reference exist in Employees table it executes successfully but now first row of Orders table has id of 2. Why it increases even when first insert statement fails

CREATE TABLE Employees
(
EmpID SMALLINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
EmpFN VARCHAR(20) NOT NULL 
)
ENGINE=INNODB;

CREATE TABLE Orders
(
OrderID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,   
EmpID SMALLINT NOT NULL,
FOREIGN KEY (EmpID) REFERENCES Employees (EmpID)
)
ENGINE=INNODB;

Don't have time to validate my statement so it is just a comment. But my guess is because InnoDB supports transaction and to full fill the ACID paradigm and the uniqueness of the `auto_incremenet` value , it is the safest way to not revert the `auto_increment` even if there is no transaction or no other transaction running. — t.niese, Nov 30 '13 at 10:36

Donato Szilagyi · Accepted Answer · 2013-11-30T15:45:42.380

AUTO_INCREMENT guarantees only that the next value will be bigger than the previous, but it does not guarantee that there will be no gap in the numbers.

There is performance reason behind this. There is a hidden counter behind the AUTO_INCREMENT, which stores the actual value. This way the sequence does not have the transactional overhead what can be considerable in a distributed transactional environment. Besides the db can prefetch more values from the disk at once into its memory, what significantly lowers the required disk io.

score 1 · Answer 2 · answered Nov 30 '13 at 10:36

When you set a auto increment a column of a table. THERE is a statistics is maintained for the next index. this will generate the next id and again it incremented and store next index to be come in next insert.

So when you try to insert with foreign key it almost have to insert and next index incremented but by your constraint of foreign key it will be perform as deleted last row.

this will be happen like that you are deleting last row and then insert new row will be increment even you delete the last row.

Why auto_incremnt column increment it value after insert statement failed

2 Answers2