CODEWITHSUNDEEP
mysqlprimary-keyauto-increment
712Jefferson
712Jefferson

Reputation: 231

MySQL - Insert Row Without Disrupting AUTO_INCREMENT

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.

Upvotes: 0

Views: 232

Answers (2)

Leo Nyx
Leo Nyx

Reputation: 696

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.

Upvotes: 2

Bill Karwin
Bill Karwin

Reputation: 562368

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.

Upvotes: 1

Related Questions