dcsimg
TODAY'S HEADLINES  |   ARTICLE ARCHIVE  |   FORUMS  |   TIP BANK
Browse DevX
Sign up for e-mail newsletters from DevX


advertisement
 

Tip: Understanding the Behavior of the AUTO_INCREMENT Column in MySQL

Auto_Increment can be tricky to use in MySQL, learn more about how it works.


advertisement

WEBINAR:

On-Demand

Building the Right Environment to Support AI, Machine Learning and Deep Learning


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.

 

Visit the DevX Tip Bank

 





   
Sridhar M S. is a Java developer from Bangalore, India. He holds a master's degree in Computer Science.
Thanks for your registration, follow us on our social networks to keep up-to-date