Page 1 of 1

How do you tell mysql to number the id properly?

Posted: Fri Oct 24, 2008 11:27 pm
by aditya2071990
I set a primary key column in my mysql table, and called it "id"; the basic idea was to use it as a serial number. But when I delete any one of the rows, say the sixth row, then the ids don't get rearranged. It still shows 5, then 7. Usually this ain't a problem, but because I am using them as a serial number for my tables when I display them, it looks awkward. Is there a simple mysql query that is able to quickly rearrange all the ids properly? And I forgot to mention, I am using auto_increment for that column too.

Re: How do you tell mysql to number the id properly?

Posted: Sat Oct 25, 2008 2:32 am
by requinix
So, you're okay with reusing old serial numbers?

What if the next Windows was called Windows 95 simply because the name "Windows 95" wasn't in use anymore?
How about making next New Year's Day be January 1st, 1009? Why not, we're not using that year anymore!

In case you didn't catch my drift, I think your idea is silly. But in case you really have to do it, issue two ALTER TABLE queries: one to delete the column, another to recreate it.

Re: How do you tell mysql to number the id properly?

Posted: Sat Oct 25, 2008 2:48 am
by aditya2071990
In my application, which happens to be a simplistic cms for a real estate site who want to list their available properties, I am not using the id column for anything really important...so I thought it was okay...

Still, thanks for the advice, and also for the hidden knowledge on how to do it :D. I will now make a new column, which is an auto_increment too, but I will not make it the primary key. I guess that settles the matter.

P.S., In case its not clear by now, the serial numbers I want are simply for 'decorative' purposes (a numbered table looks dubiously decent), and they don't bear any significance, either in the application or the viewers mind...

Re: How do you tell mysql to number the id properly?

Posted: Sat Oct 25, 2008 4:18 am
by onion2k
Removing the column and then recreating it will only fix it once. The next time an id is deleted they'll be wrong again. A better solution is just to echo $i++ instead of the id.

Re: How do you tell mysql to number the id properly?

Posted: Sat Oct 25, 2008 4:48 am
by aditya2071990
Hmm well, I can make the script do the deleting and recreating the column every time an id is deleted....

But instead of nasty mysql queries for every silly thing, and i++ would be nice indeed... I will try and implement that.... Thanks!

Re: How do you tell mysql to number the id properly?

Posted: Sat Oct 25, 2008 5:09 am
by Hannes2k
Hi,
you never should change the key for a data record. If you do so, you had not understand the aim of using keys. A key should identify the record, and not just until the next delete operation is done.
Imagine your social security number is changed every time a person die...

Using $i++ is here the best solution.

Re: How do you tell mysql to number the id properly?

Posted: Sat Oct 25, 2008 9:33 am
by aditya2071990
Thanks for the reply :)