0

I have a table called "posts" and it contain 500 posts but the ids are not sequence like:

1
3
9
22
446
....
etc.

That's because I deleted some of the posts from the table.

So how can I re-correct the ids?

Daniel Rikowski
  • 71,375
  • 57
  • 251
  • 329
Pin Cody
  • 135
  • 3
  • 12
  • 9
    Why do you need to do that? – Mat Oct 22 '11 at 13:53
  • 2
    Yeah, you probably don't need that at all. – Stanislav Shabalin Oct 22 '11 at 14:00
  • 3
    +1 @Mat - you should never ever ever need to alter the value of your primary key column, because you should never use that data anywhere, for anything, other than a reference to the row. – DaveRandom Oct 22 '11 at 14:00
  • Dup of [MySQL: Reorder/Reset auto increment primary key?](http://stackoverflow.com/q/740358/), [How to make primary key serially?](http://stackoverflow.com/q/5964017/90527). – outis Apr 28 '12 at 22:04

3 Answers3

3

Primary Key IDs are not supposed to be changed, especially when they are referenced in other tables.

If you need a property that is like a row number, you can add another field for that.

For example invoices are numbered, but the invoice number should not be the primary key, since you want the freedom to re-number one of them without losing other connected information, such as invoice details in other tables.

stivlo
  • 83,644
  • 31
  • 142
  • 199
1

The easiest way to fix it is to create a quick script to loop through the table and update that the id column and then run on your database: ALTER TABLE tbl AUTO_INCREMENT = 100;

Ziminji
  • 1,286
  • 1
  • 14
  • 18
  • Please note that I do agree with some of the other answers when it comes to never changing the id unless you have a specific reason to do because of the reasons they gave. – Ziminji Oct 22 '11 at 14:18
-2

NEVER EVER CHANGE THE ID!

Id is something the record borns with and dies with. That's why it's called id, it is an IDENTITY!

As in real life you cannot change the identity of things, you won't do it in database.

It is a very bad idea from the philosophic perspective, which also results in practical problems. Even if you would renumber the ID in all your tables in your database, the old IDs might still survive somewhere (and make a big mess then):

  • in URLs all over the internet
  • in your logs
  • in your backups
  • in other database copies.

Also, ID must serve only for identification and nothing else. For example: you use IDs to define order of some dictionary, which you normally present sorted. Then you need to add a new item, which must be presented between items with id 20 and 21. The BAD solution would be to change ID for records with ID >= 21. The GOOD solution is to add a new column Order, which defines the order of items and can be changed whenever needed.

Remember:

ID must serve only for identification and nothing else!

NEVER CHANGE THE ID!

Tomas
  • 57,621
  • 49
  • 238
  • 373
  • ID stands for index, but beside that, I agree. – SteeveDroz Oct 22 '11 at 14:09
  • @Oltarus, nope, "ID" was always derived from "identity" or "identification". Index is not the principle itself, index is just some technical widget. ID has much more broader sense than just databases. Also, much more broader than computer science, ID also stands for your passport :-) – Tomas Oct 22 '11 at 14:27
  • 2
    While altering the table ID is generally a bad idea, there are reasons it may need to be modified which this answer does not consider. Try not to make such overstatements. – Ziminji Oct 22 '11 at 14:46
  • @Ziminji, there are hardly such reasons. I claim that in 99.99% cases when you want to change IDs you are doing something wrong. You should consider that you are giving advices to beginner, you should try to guide him and tell him if he's trying to do something wrong! Not just blindly supply him solutions that will misguide him to future problems! – Tomas Oct 22 '11 at 14:58
  • @Ziminji, did you downvote me? Was it a revenge? I haven't registered any factual critics on my post. – Tomas Oct 22 '11 at 15:01
  • 1
    @TomasT. No, it was NOT revenge.... I apologize if you thought so but I just thought you answer was overstated because the author of the question MIGHT be trying to do something that requires him to do so for whatever reason that is not apparent in the question. Rather, your answer makes an assumption that this should NEVER be done which therefore doesn't answer his question. – Ziminji Oct 22 '11 at 15:13