Wednesday, April 24, 2013

Setting auto increment correctly in mysql tables

If you are uploading data in batch mode and don't want to physically type in the values for the primary key that auto increments, you probably have to reset your table definitions:

create table XXX
(  Name varchar(30) not null ,
   Type varchar(20) not null,
   ID int not null AUTO_INCREMENT,
   Primary KEY (ID)
But if you are working with Phpmyadmin, you can certainly supply these commands in the sql box. But there is also another easy way. Log into phpmyadmin as the owner of the table and click on structure. You will see every column has column definition available for you to edit. Click on change link for the column you wish to change, in this case ID column, and check on a box that has constraint A_I. Then save it. Now you are ready to roll. 
Now every time data uploaded it remembers the last auto-increment value and increments from there. In order to reset to 0 or any other number, say the following at the mysql prompt: Alter table XXX auto_increment=n; Now it will update auto increment value from that number.

No comments:

Post a Comment