[SOLVED] auto incrementing record not resetting upon delete command???

[calmchess]

Hi I am using ID as a auto incrementing primary key  however if i goto the mysql command line and issue the command ....delete from myTable; then everything is deleted from the table but the ID property slot doesn't return to 1 how do i force it to return to 1?

[AndyB]

There's really no reason to mess with the auto increment index value.  Much safer to pretend it doesn't even exist.  Why do you want to reset it?

[calmchess]

well the table is a test table and I use where statements using the ID field instead of username for building generic modular scripts. so when i clear out the table using the delete command the ID picks up where it left off ....i want it to start over at  0 or 1 ....*shrugs* whichever it uses.

[PFMaBiSmAd]

Use - TRUNCATE [table][tr][td] tbl_name

 

TRUNCATE TABLE empties a table completely. Logically, this is equivalent to a DELETE statement that deletes all rows, but there are practical differences under some circumstances.

 

For InnoDB before version 5.0.3, TRUNCATE TABLE is mapped to DELETE, so there is no difference. Starting with MySQL 5.0.3, fast TRUNCATE TABLE is available. However, the operation is still mapped to DELETE if there are foreign key constraints that reference the table. (When fast truncate is used, it resets any AUTO_INCREMENT counter. From MySQL 5.0.13 on, the AUTO_INCREMENT counter is reset by TRUNCATE TABLE, regardless of whether there is a foreign key constraint.)

 

Also, you and your coding should not care what the values are (or that there will be gaps when records are deleted.)

[calmchess]

yeah absolutely i was asking this question more for informational purposes than for practical purposes....you answered my question and cleared my confusion................... thanks....this is a really great forum.