After deleting MySQL table records...

[theHollow]

I have a table with 3 columns (id, text, and status) the id is primary key and is set to auto increment. When I delete a record (say record 3) and I have 5 record total, I am left with a gap in the id column. Is there a way to stop this? So when 3 is deleted, 4 becomes 3 and 5 becomes 4.

Brendan.

[bwh2]

why would you want to do that?

[theHollow]

So there is no gaps in the id's. I dont need to do it though. So if theres no way to do it, it doesnt matter

[skOOb]

is this table being related to another table using the id field? there is no reason to keep the id's that way unless you need it to be that way for some obscure reason. if you need to display your information on a page, you could just don't use the id field, you can just use a counter to display them in order.

[bwh2]

it's certainly doable. but it's a pain and there's no reason to do it. in fact, it's probably a really bad idea to do it. by changing a row's ID number, you are going to break down any dependencies on that ID. so if you have any other tables, URLs, etc. that depend on that ID number staying constant, you will break those systems. and if you don't have those relationships, there's no current need for an ID.