3

I have a table - with id which is auto_increment and a email column which is defined to be UNIQUE.

Consider the auto_increment to be set to 1.

In my stored procedure when I INSERT a new row with a unique email id the auto_increment column is incremented. Correct behavior.

Now when I try to INSERT the same email again UNIQUE KEY is violated. I check this and do a ROLLBACK. However the auto_increment is set to 2 now. Why?

In the third attempt of INSERT, this time with a unique email the row is inserted however the id field has value 3. Not good.

Is there a way I can decrement the value back to 2.

rkosegi
  • 14,165
  • 5
  • 50
  • 83
JHS
  • 7,761
  • 2
  • 29
  • 53
  • 1
    There is a way and you don't want to do it because you'll violate your data integrity. "Wasted" auto_increments are fine. Their purpose is not to give you pretty sequential numbers. Use triggers if you need that and create a procedure that'll use another column which'll provide that functionality. – N.B. Jun 05 '12 at 13:30

1 Answers1

5

Check this: MySQL AUTO_INCREMENT does not ROLLBACK

The most salient point being: You should never depend on the numeric features of autogenerated keys.

Community
  • 1
  • 1
Jeremy Goodell
  • 18,225
  • 5
  • 35
  • 52