How do you tell mysql to number the id properly?
Moderator: General Moderators
- aditya2071990
- Forum Contributor
- Posts: 106
- Joined: Thu May 22, 2008 11:30 am
- Location: Hyderabad, India
- Contact:
How do you tell mysql to number the id properly?
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?
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.
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.
- aditya2071990
- Forum Contributor
- Posts: 106
- Joined: Thu May 22, 2008 11:30 am
- Location: Hyderabad, India
- Contact:
Re: How do you tell mysql to number the id properly?
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
. 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...
Still, thanks for the advice, and also for the hidden knowledge on how to do it
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?
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.
- aditya2071990
- Forum Contributor
- Posts: 106
- Joined: Thu May 22, 2008 11:30 am
- Location: Hyderabad, India
- Contact:
Re: How do you tell mysql to number the id properly?
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!
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?
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.
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.
- aditya2071990
- Forum Contributor
- Posts: 106
- Joined: Thu May 22, 2008 11:30 am
- Location: Hyderabad, India
- Contact:
Re: How do you tell mysql to number the id properly?
Thanks for the reply 