Wednesday, November 6, 2013

how to Resetting the primary key to 1 after deleting all the data

You could drop the primary key column and re-create it. All the ids will then be reassigned, I assume in the order in which the rows were inserted.
However, I'm not sure why you'd want to do this, especially if you have other tables that have a foreign key to this table. If so you would need to update those fields as well, in which case dropping and recreating the column wouldn't be a good solution. You could instead remove the autoinc/primary key property, then create a new autoinc primary key column. You then would have both the old and new ids which you could use to update other data elsewhere.
you may start you increment through below the statement

alter table foo AUTO_INCREMENT = 1

This will reset the Auto Increment on the table as well as deleting all records from that table

No comments:

Post a Comment