Tuesday, March 25, 2008

MySql: to reset auto increment number

Recently I've been doing a project using MySql database server. I created some tables using auto increment number as the Primary Key. Just like the name suggests, the number will increase every time I insert new record into the table. At some point I wanted to delete the whole dummy data I inserted.

Using the 'DELETE FROM Table_name'statement will only remove all records but will not reset the auto increment number back into 1. After some little effort of searching I found this useful syntax to reset the number:
ALTER TABLE Table_name AUTO_INCREMENT = 1;

Problem solved.