How to reorder MySQL auto increment primary key

1158280 pts.
Tags:
Database
MySQL
I'm using MySQL and in my table, I have an auto increment primary keys. I already deleted several rows in the middle of the table. Now, I need to reorder the keys but I'm not sure how to do it. Can someone help me?
1

Answer Wiki

Thanks. We'll let you know when a new response is added.
SET @count = 0;
UPDATE `table_name` SET `table_name`.`id` = @count:= @count + 1;
ALTER TABLE table_name AUTO_INCREMENT = 1;

Discuss This Question: 3  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.
  • simo2elyousfi
    HELP (How to Reorder/Reset auto increment primary key with foreign key)
    EXp: I have two tables like below. Person table with primary key and address table with primary key and the foreign key with the id of person table's primary key. Both tables have missed some rows in between like 1,3,6.... some rows(4 and 5 ) have been deleted. Now I need to rearrange primary keys in both tables and also after changing primary keys, foreign keys also need to be changes as to new primary keys.
    10 pointsBadges:
    report
  • carlosdl
    Why do you think you need to rearrange them?

    Primary keys are not meant to be modified, and since this is most likely something that will occur regularly, you will have to be modifying them constantly, which is a bad practice.

    Even though your keys are numbers, they don't really need to be consecutive and gap-less.  As long as they allow you to uniquely identify a row, they are fine.
    85,925 pointsBadges:
    report
  • ToddN2000
    @simo2elyousfi: Like they say if it ain't broke don't fix it. Are they causing the application to not work properly? The problem you may run into is if other application are set to use the old primary / foreign keys.  Reordering your table may break those applications. 

    Leaving gaps in the range has never been an issue with anything I have worked on over the years. Then again I have not seen your specific needs. Why do you need to change the keys?
    136,550 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

Thanks! We'll email you when relevant content is added and updated.

Following

Share this item with your network: