record numbering sequence problem

Hello,

I am new to sql so you will have to forgive my questions. I have used MS ACCESS quite allot. When I create 3 records and delete 1 record then add another one the numbering goes out of place.

example:

I create 3 records with the id 1,2,3 I delete record 3, then in the future add another record, it then goes 1,2,4.

Is there anyway in MYSQL to make the numbering go in sequence, if I delete records?.

Thank you for your time.

------------------

I assume you are talking about auto_increment? If so, what you describe shouldn't happen. If you delete the record with the highest value then the next record inserted should have that value. However, if you delete other than the highest value it won't be replaced. You could reorder with:

delete from table_name where id=$id;
update table_name set id=id-1 where id>$id;

However, that is a bad idea usually. If you have dependencies in other tables based on that id you will create anomolies.

>However, that is a bad idea usually. If you >have dependencies in other tables based on >that id you will create anomolies.

Thats true I never thought of that!, thank you.