Most of us are familiar with AUTO_INCREMENT, and have used it in at least one context. However, the following behavior is a little tricky, especially when we fail to observe — and most of the time it is not reproducible due to the behavior or MySQL.

Consider the table that is created as below.

CREATE TABLE EMPLOYEE (EMP_ID INT PRIMARY KEY AUTO_INCREMENT,FIRST_NAME VARCHAR(25),LAST_NAME VARCHAR(30));

Observe that the column EMP_ID has the property to AUTO_INCREMENT.

Now, execute the following:

INSERT INTO EMPLOYEE(FIRST_NAME, LAST_NAME) VALUES('Lope','Dey');

A record has been inserted with EMP_ID as 1.

Now use and UPDATE statement and set the value of 1 to 2. Essentially, you are trying to understand the way MySQL will work with this value.

Now, execute the following:

INSERT INTO EMPLOYEE(FIRST_NAME, LAST_NAME) VALUES('Rina','Well');

We get an error indicating that there is a duplicate entry for the key PRIMARY.

Now, execute the following again and the error does not show up anymore.

INSERT INTO EMPLOYEE(FIRST_NAME, LAST_NAME) VALUES('Rina','Well');

The AUTO_INCREMENT value has been further incremented and a new number, i.e., 3 is now used and the record gets inserted successfully.

This is a good thing as well. But in case of errors observed, you may not be able to reproduce it. Be careful with UPDATES on AUTO_INCREMENT column values.