Table of Contents
MySQL error – Duplicate entry ’34’ for key ‘PRIMARY’
Check this situation – you want to import to existing MySQL table already prepared data in CSV in e.g. PhpMyAdmin, and then you can see MySQL error:
#1062 – Duplicate entry ’34’ for key ‘PRIMARY’
But you take a look in MySQL table and you can see that last “id” value is, let’s say, 30.
What went wrong? Probably last 4 rows in your table were deleted earlier, so last MySQL table row’s value is 30, but the last value of AUTO_INCREMENT is exactly 34. To check it you must go in PhpMyAdmin into structure of the MySQL table and at very bottom of page is table “Rows statistics” like on image below, and there is field “next autoindex” which tells us that the “value of the last auto increment” was, in this case, 34 (because next will be 35).
Check how to change value of next auto_increment in MySQL:
So we know how to check the current auto increment value (which corresponds in most cases the row’s ID value). But let’s focus now on how to change the value of next autoindex.
No worries! It is very easy! Just use syntax like below in SQL request (in PhpMyAdmin in wanted table click on “SQL” tab and enter code below there into textarea field). Of course, in place of
[number] put the right number you want to set as next auto increment value.
ALTER TABLE table_name AUTO_INCREMENT = [number];
In my case it will be the following MySQL code to change next auto increment value:
ALTER TABLE my_table AUTO_INCREMENT = 30;
You can see the change in table structure tab, in “Rows statistics” as on image below:
… and it is finished now! You know now how to change the autoindex or “auto increment” value in MySQL table using PhpMyAdmin. I hope that this article was helpful for you.