0

Possible Duplicate:
Fragmentation of id's (auto_increment column) in mysql

I have a news portal and each news has its id now counting 515 articles in MySql

But some days ago I did some kind of a mess so when adding the news the id is showing 2339 and I always have to manually edit to 516.

Is there a way I can fix it so next article will be with the id 517 instead of 2340?

Community
  • 1
  • 1
Ingþór Ingólfsson
  • 345
  • 2
  • 6
  • 14

2 Answers2

3

A primary key should be used solely for mapping relationships and identifying rows uniquely. Unless you have set up your ID field to also be a foreign key then reordering is likely to break much of your database functionality.

In short yes what you ask is possible, but is NEVER a good idea in database design. Don't let vanity get in the way of building a good database.

Now if for some crazy reason you still want to do this, your best bet is to use a PHP script to select * and order by ID from the table. Then have it loop through and update each row's ID column to whatever you want (e.g. start at 0 and increment). The problem you will most likely run in to here is that a primary key is unique and your IDs are not in perfect incremental order so it is likely you will have to make ID a regular column first and switch it back to a primary key or unique key after the operation.

George Reith
  • 13,132
  • 18
  • 79
  • 148
1

I think you're just trying to change the current index of your auto incrementing id?

If you have PhpMyAdmin, you can click on your table, then go to the "Operations" tab. You'll see a field called "AUTO_INCREMENT". You'll want to change this to 517 instead of 2340.

Or, using SQL:

ALTER TABLE articles SET AUTO_INCREMENT = 517

EDIT: I see a lot of comments saying it doesn't matter at all. It's true, it doesn't affect anything, and probably should never be done. However, I have at least provided a solution if you still decide to move forward with it.

Jim D
  • 988
  • 10
  • 18