I hope I can explain this. I have a table which contains a field named ID. This is set as PRIMARY. Because users registered on my site and after that deleted their accounts (so a lot of rows got deleted, too) the table rows now are jumping, for example, from ID 33 to 3333.
What I want to do is to rewrite, somehow, the ID field, so the rows will start from 1 and end at the last row. Is there a way to do this?
Thanks!
RobotFX 0 Junior Poster in Training
debasisdas 580 Posting Genius Featured Poster
the table rows now are jumping
I really do not understand what exactly you mean by that, are you trying to re arrange the ids starting from 1 and increment by 1 till the last record ?
RobotFX 0 Junior Poster in Training
Yes, exactly. Now I have ID from 1 to 10, for example, then the next is 15 to 20, then again after 20 is 33 to... etc. I'd like them to rearrange them so they start from 1 and end with the last row number; now I have 15000 rows but the last row has the ID 223000 - it should have the ID 15000.
debasisdas 580 Posting Genius Featured Poster
I want to understand what is the problem if the id values are not continuous. As you said this field is the PK of the table, is it not referred by any other table ?
RobotFX 0 Junior Poster in Training
No, the database has only one table. I don't know if there's a real problem, I just want to 'keep things in order' :) Besides, maybe it will make the database a little smaller and faster, too.
debasisdas 580 Posting Genius Featured Poster
If the records are already deleted and the ID field is the PK of the table it is not going to have any effect on size or performance.
RobotFX 0 Junior Poster in Training
I don't want to sound like an ungrateful bastard, but are you trying to help me with what I want or not?!
Anyway, for the posterity: apparently is very easy to do this, it took a noob 2 days to realize :) - Solution: just delete the field and create it again, as PRIMARY.
debasisdas 580 Posting Genius Featured Poster
Your post shows you are new to using database so trying to use an unrealistic approach.
I don't see a single benefit of this approach, neither size nor performance wise.
What you will do when again some of these (modified) ids gets deleted, you again have to maintain the data. I think this is the only outcome of this process, more frequent maintenance.
If you still think i was not trying to help you and you are adamant to use your approach, god bless.
Be a part of the DaniWeb community
We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.