Why AUTO_INCREMENT field keeps previous values

[Mike Kypriotis]

I have a table with 2 rows so AUTO_INCREMENT biggest value is 2, I delete those 2 rows (table is now empty) and I insert another two but instead of AUTO_INCREMENT having values of 1 and 2 (as I wanted) it has 3 and 4 (in the insert statement I do not want to mention AUTO_INCREMENT field at all) any ideas how to correct it?

[Rabbit]

It keeps it for referential integrity. If you use that auto-increment field as a foreign key in another table, you don't want it to reset every time. Otherwise you end up with really bad data.

You can reset the auto increment field with

ALTER TABLE tableName AUTO_INCREMENT = 1;

[Mike Kypriotis]

true, since I wanted every time I empty my table to reset my key (to prevent from getting really big values for nothing) instead of a DELETE I used TRUNCATE (which resets the auto-increment)