Tuesday, September 15, 2009

MySQL: Set Auto Increment Value of a Table

I have come across many times where in we keep deleting rows from behind the database and when we run the application, suddenly things are not working.

One Main reason for this is some reference table is still incrementing vaules but the main table would have been gone ahead increment.and hence the mapping between the tables have gone haywire.

So to fix this it is necessary to reset our tables AutoIncrement(auto-index) Values.

Query :
ALTER TABLE table_name AUTO_INCREMENT = start_value


Eg:
If you want to start a increment from value 100 then query is as
ALTER TABLE table_name AUTO_INCREMENT =  100 

Incase you want have cleared all the data from the table , and want the table to start the increment from 1,
TRUNCATE TABLE table_name 

Note :

When TRUNCATE is fired if there are any existing triggers of ON DELETE will not be fired

1 comment:

Express you views