clearing a table's primary keys in MySQL

[TheSeoGuy]

This may be a silly question, but is there a way to clear out primary key's in a table so that the next entry starts over at 1 again?

We have a database that we have done a lot of testing in, and now that we want to go live we would like to have all the keys start at 1.

Do I have to drop and recreate all the tables or is there a way to do this via a command?

Thanks in advance!

[coopster]

I'm assuming you have run something like

DELETE FROM table;

and it is completely empty, but every time you try to insert data, it is AUTO_INCREMENTing from the stored value from previous data...correct? If so, just

ALTER TABLE table AUTO_INCREMENT = 0;

and you are good to go.

Relative thread that may come in handy...

[webmasterworld.com...]

[TheSeoGuy]

Coopster,

Thanks! You have helped me out in the past as well... Thanks so much!

[dreamcatcher]

I think

TRUNCATE table;

also works.

[coopster]

You're right, dreamcatcher, using

TRUNCATE

will start

AUTO_INCREMENT

from the beginning value found in the table definition -- but only from MySQL >= 4.0. And there are differences on how things work depending on the storage engine being used. Rather than list them here, I would encourage anybody using TRUNCATE [dev.mysql.com] to read the manual pages first.