Question about deleting records

chelsea7

Hello everyone!

I have a question about deleting records. I having a little problem when I delete or empty records in a table. What is happening is after I do this, when I want to insert a new record either through PHP or at the prompt, the auto increment continues where it left off at from the last record that was previously deleted.

ie. 200 was the last record. The table now has no records. I inserted a new record. Rather than it reset to 1 it starts at 201.

Does anyone know why this is happening?

Thanks
Chelsea7

Sxooter

Yes, that's how autoincrements are supposed to work.

They're not record numbers (ordinal) they are just record ids, and it's considered bad form to reuse them.

portaloo

Hi Chelsea,

Yep it does and as far as I am aware there is no way of changing that.

I suppose if you wanted to, you could leave out the auto_increment for the primary key and every time you want to insert a new record count the number of records then increment that by 1 and you will always start at 1 when you empty the table.

I wouldn't recommend that personally though.

Drakla

ALTER TABLE $table AUTO_INCREMENT=$num;

If you're using MySQL - but listen to what everybody else says - unless your table is for something really stupidly trivial then never rollback your auto increment as it's real bad jam as far as data integrity works.

Resetting it also has limitations if there are values still in the table, but if you're clearing the whole thing you're fine.