-1

so I created a table with a column which I declared EmployeeID as a primary key int with auto increment, but here is the problem:

If I delete a row and then insert a new one the new increment will still count For example:

ID Name
1  jayden
2  karen

delete karen ...

ID Name
1 jayden

insert new one

ID Name
1 jayden
3 nicolas

So it basically skips the previous ID.

Suresh Kamrushi
  • 15,627
  • 13
  • 75
  • 90
blackJuuls
  • 3
  • 1
  • 5
    This is expected behavior and by design. What's the problem? – David Oct 20 '19 at 14:18
  • Possible duplicate of [What happens to autoincrement primary keys after delete?](https://stackoverflow.com/questions/5198656/what-happens-to-autoincrement-primary-keys-after-delete) – Himanshu Oct 20 '19 at 14:49

4 Answers4

1

This is not a mistake, it is how MySQL works with auto_increment. It stores the last value which it gave at the insertion and increments it. You can always get the last ID at your next insertion

insert into yourtable(id, Name)
select id + 1, 'foo'
from yourtable
where id = (select max(id) from yourtable);
Lajos Arpad
  • 64,414
  • 37
  • 100
  • 175
  • be aware that the `where id = (select max(id) from yourtable)` part might cause problems with the wrong transaction isolation level .. Should be fine to use with `SERIALIZABLE` isolation level to prevent the `max(id)` be changed by a other insert transaction while this query is running.. Meaning you are most likey better off with `SET @max = (select max(id) from yourtable)` and `insert... where id = @max` to release the lock a bit faster.. – Raymond Nijland Oct 20 '19 at 15:04
0

This is expected behavior from the 'delete' command What would you ever want/need an Unique Identifier that can be reaffected to someone else?

The 'Truncate' command will delete all your data and reset the ID, maybe this is what you are looking for

Richard Matriche
  • 87
  • 7
0

That's because an auto increment is a independent property of your table, that is only used when assigning new IDs and does not depend on the last existent ID on your table. This is how many relational databases, not only MySQL, were designed for.

To achieve what you want (which seems to be having IDs in sequence without "holes") you would need to query the last ID and increment on it to be set manually.

Ex:

SELECT id from Users ORDER BY id DESC LIMIT 1

Or instead setting the auto_increment of your table according to your last id.

Be aware that both of this is not performatically wise. You should really stick to the auto increment default behavior, since it's optimal, unless you have some strong reason for not doing things as they were designed to be done.

Matheus Gomes
  • 434
  • 2
  • 11
0

After delete a row use this query, ALTER TABLE tbl_name AUTO_INCREMENT = ID;

Actually this is how MySQL works and it's not a mistake

Wijayanga Wijekoon
  • 61
  • 1
  • 9