MySQL - ID issue

Question

The basic problem, I have a column called 'ID' which is set to AUTO_INCREMENT and PRIMARY index.

If I add a row it sets the ID to 1, next row to 2, etc. It's working fine.

But when I have for example 6 rows with IDs: 1, 2, 3, 4, 5, 6 and I delete row with, for example, ID = 6, so it's: 1, 2, 3, 4, 5 now, and I add a new row, it doesn't get ID = 6, instead ID = 7. So it's: 1, 2, 3, 4, 5, 7. What's t he problem?

Why do you downvote my question? I just wanted to know...

Maybe you could use this in some way: http://stackoverflow.com/questions/8923114/how-to-reset-auto-increment-in-mysql :) — Marc, Mar 27 '15 at 20:38
You should never design any system so that it's dependent on consecutive primary key values, only on unique primary key values — Mark Baker, Mar 27 '15 at 20:39

score 0 · Answer 1 · answered Mar 27 '15 at 20:43

The autoincrement (AI) column is supposed to behave that way. The running count of the AI becomes part of the table definition. You can run show create table "yourtablename" and see that after the Engine definition there will be an AI count.

You can run ALTER TABLE tbl AUTO_INCREMENT = 6; to set the AI back to 6 if you delete the 6th row. Although, this is not recommended.

MySQL - ID issue

1 Answers1