[SOLVED] Primary id sequential

[ksb24930]

I have id, the primary key, set to auto-increment. But, I want new rows to fill in the empty rows. How can i do that? For example, I want my id to be 1-20 without spaces, without numerical gaps.

[mikesta707]

well, you would probably have to do a query, get all the id's from the table, and process the information to find any gaps. once you do that, you can just set any new ids as in between those gaps, but that would kind of defeat the purpose of auto-increment because you wouldn't even really be using its functionality if you did that. Can I ask why you need there to be no gaps?

 

 

Edit: There may also be some fancy SQL commands you could do, but i'm not sure about that

[lemmin]

If you are using MySQL you can drop the primary key column and put it right back in. This will renumber all rows starting at one and the next one will be the next number in the sequence.

[akitchin]

resetting the primary key, or filling in gaps, ruins the referential integrity of the database. the whole reason that gap is there is because there once was an entry, and is no more - that points to the fact that any other records using that key in their entry are orphaned. it's important to know these things, and if it isn't, then having that key is a little pointless in the first place.