MySQL - Insert Row Without Disrupting AUTO_INCREMENT

Question

Quick question:

I have a sports league database with a list of games (let's say 40 or so). Each game is auto-assigned an ID number as the primary key when importing the entire schedule from a spreadsheet. The games are then displayed on the web page in descending order thanks to this invisible (to the user) primary key. Here's an example: League Schedule

Works great. The only problem is that sometimes the games are rescheduled and moved to a later date or a new game is added and has to be inserted into an already existing schedule. To this point, I've had to manually edit each affected row's ID (using PhpMyAdmin) to account for the changes and this can be quite tedious and time consuming.

What I'd really like to do is set the table to readjust primary key values on the fly. Meaning, if I inserted a brand new game into the fifth row of the table, all games thereafter would automatically be readjusted (ID 5 would become 6, ID 6 would become 7, and so on).

Is there a way to set-up the table to do this, or a particular SQL command I can use to accomplish it just the same? Apologies if this has already been asked many times in different ways. Any and all feedback is appreciated.

Leo Nyx · Accepted Answer · 2013-09-23 19:14:38Z

2

You should not use your PRIMARY KEY for that. Add a special column like sort with a regular INDEX, not UNIQUE. It does not have to be INT either, you can use real numbers. This way you will always be able to insert new row between any two rows of your schedule.

answered Sep 23, 2013 at 19:14

Leo Nyx

6963 silver badges5 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Bill Karwin · Accepted Answer · 2013-09-23 19:06:08Z

1

No, auto-increment is required to be unique, but it is not required to be in any particular order or even contiguous. The fact that auto-increment is monotonically increasing is only by coincidence of its implementation. Don't rely on the values being in chronological order.

Trying to adjust the values is not only manual and awkward, but it risks race conditions, or else would require locking a lot of rows. What if you insert a row with id 5, but your table has 1 billion rows greater than id 5?

There's also a risk of renumbering primary key columns, because any user who got an email telling them that they need to go to game 42 may end up going to the wrong game.

If you have need to view the rows in a particular order (e.g. chronological), then use a DATE column for that, not an auto-increment column.

answered Sep 23, 2013 at 19:06

Bill Karwin

567k87 gold badges709 silver badges869 bronze badges

1 Comment

712Jefferson Over a year ago

"What if you insert a row with id 5, but your table has 1 billion rows greater than id 5?" Exactly.. what a pain in the ass! Thanks very much for your helpful feedback.

Collectives™ on Stack Overflow

MySQL - Insert Row Without Disrupting AUTO_INCREMENT

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related