MySQL ID Sorting

[Anon]

I've made a news script that adds, edits, and removes news articles. When the script creates a news article it inserts all the relavant information, and the database automatically gives it an id.

My problem is that when i remove a news article the ids of the news articles remaining in the database stay the same. What i want is that when an article is deleted the ids will rearrange themselves.

Say there are 8 articles and i delete article 1, there is a gap where 1 was.. I want the articles arrange to fill the gap.

Thanks,

Mick
[e-mail]
http://www.micksworld.com

[Anon]

I don't recommend you to do this. Imagine that, in another table, you use that id in another table (say identifying images from the article). If you alter the id, the db will lose its integrity, because what used to be article 8 will now be article 7. This way you can end up with an article about the weather showing pics from a PHP-party (to put an example). If the id is a key only for that table and has no relations in other tables, you can do it (i don't recommend it, anyway)

It's not a good idea to alter an auto-increment index this way. My advice to you is that you leave it in it's place like it is now. I know the gaps look ugly, but the results if u use that id as a foreign key will be even uglier.

Hope this helps

fLIPIS

[quicknik]

reorganizing primary keys on deletion is ugly and risks database consistency

and imo it's totaly unecessary...

bye,
niko

[theslinky]

Hi,

I agree with the above two posts: don't alter anything in the Database.

If you're worried about looks of the ID and the numbering, use your own numbering in your front-end HTML/PHP files.

Start a counter going from 1 to the # of rows there are. This counter will be indepenedent from the mysql auto-increment and it will make your front-end look better.

Also, even if you DO change the ID #s in mysql, you're going to be putting heavy load on the database for an unnecessary reason. Think about it: if you have a thousand entries and you delete the first one, you have to change 999 entries in the table and also the many other entries in other tables you've created relations in.

Perhaps we (or just I) are not understanding your concern/question. Why do you want to "fill the gap"? if you are sorting using the ID field, then you don't have to worry. suppose you have 5 entries and you delete #3. If you add another entry, it gets an ID of 6 (NOT 3). So the order by ID still has its integrity.

-sridhar

[Anon]

Ok, well thanks heaps for your help... i'm not going to bother with organizing the id #s to fill the gaps..

Thanks again for your help.

Mick
[e-mail]
http://www.micksworld.com