13

i have a table with ID , this ID is auto increment and primary key ,the first time i inserted to that table , the ID starts from 0 , but after i removing all the values from that table and inserted again to it , the ID doesn't start from 0 , but starts from the last value that ID had , what is going wrong please ? can i reset the value to 0 ?

LinCR
  • 391
  • 3
  • 6
  • 14
  • 3
    Nothing is wrong. This is expected behaviour. Does your car's odometer start from 0 every time you go for a ride? – ypercubeᵀᴹ May 25 '12 at 11:22
  • and so what can i do , i inserted more than 300 times to my tables, when i saw the homework to the teacher it is not a good thing to show him clients starts from 301, right ? – LinCR May 25 '12 at 11:24
  • 1
    If you made the inserts from a script, you can drop the table and then re-run the script. If the Inserts were manual, you would't want to re-insert, would you?. Anyway, you had 300 temporary/test clients that you deleted. If I were your teacher, it wouldn't be a problem. – ypercubeᵀᴹ May 25 '12 at 11:33

4 Answers4

18

You may either truncate the table using

TRUNCATE `table`

Truncate will delete all of the data in the table. Note that using TRUNCATE will also not fire any DELETE triggers like DELETE.

Or you can reset the count using

ALTER TABLE `table` AUTO_INCREMENT = 1

It is however the expected behavior. Don't do the latter unless the table is truly empty.

Ben Swinburne
  • 25,669
  • 10
  • 69
  • 108
5

To insert a primary key with value 0, execute first the following request :

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
Ace
  • 51
  • 1
  • 1
3

Removing all the elements in a table does not affect the primary key. The primary key will still autoincrement from its last value.

If you want to reset the primary key, you can try to truncate the table:

TRUNCATE TABLE yourtable;

This clears all the elements from yourtable. Whether it resets the primary key depends on your database. With Mysql it should work, as Mysql deletes and recreates the table on TRUNCATE.

nina
  • 76
  • 5
1

You have to use the TRUNCATE statement to restart the auto_increment from 1. It's the normal behavior to avoid braking things.

dAm2K
  • 9,923
  • 5
  • 44
  • 47