0

Had a search for this but the solutions I found did not take into account the fact that I have an ID in the table which is higher than what I want the auto increment to reset to.

Basically, I have a table of dog breeds, which are pulled out into a form in my site for users to select, and currently there are two:

----------------------
| ID | Name           |
----------------------
| 1  | French Bulldog |
----------------------
| 2  | Labrador       |
-----------------------

But I also need to have an "Other" record, which ALWAYS needs to be last in the list of breeds to be pulled out on the front end. So, I have assigned an ID of 9999 to the Other breed, so the table looks like this:

------------------------
| ID   | Name           |
------------------------
| 1    | French Bulldog |
------------------------
| 2    | Labrador       |
-------------------------
| 9999 | Other          |
-------------------------

But I want to be able to add other breeds to the table, but I'd want the auto increment to start from 3. So I used the following:

ALTER TABLE breed AUTO_INCREMENT = 3

It doesn't return any errors but when I add a new entry, it begins at 10000 instead of 3. This means that "Other" will always be the third in the list and not last as I need it to be.

Is there any way of making it begin increment from 3 despite the fact there is an ID of 9999 already in the table?

Michael Emerson
  • 1,774
  • 4
  • 31
  • 71
  • Why do you need to set the ID for Other? If you're doing this to order/sort the table, use a second column for sort order, and set Other to a high value there instead – WillardSolutions Jan 10 '17 at 17:33
  • Because in the code, the select box that houses the breeds has a value of '9999' against other, because if it doesn't Symfony will regard this as an invalid value since it doesn't exist as an ID in the table. – Michael Emerson Jan 10 '17 at 17:35
  • I think rather than trying to hack MySQL to do something weird, you're better off refactoring your frontend to handle the "Other" option – WillardSolutions Jan 10 '17 at 17:37
  • If you need "magic" numbers, it is best to either not use an auto-increment field, or use the lowest numbers for such values (leave yourself space and start the "dynamic" entries at 100 or 1000 and use 1 for 'Other'). _Don't use 0 though; in the event a table repair is needed, mysql loves to "fix" such values._ – Uueerdo Jan 10 '17 at 17:39
  • 1
    The `AUTO_INCREMENT` option on an integer column is just a way to create unique values, making the column ready to be used as Primary Key without human intervention. Don't try to use it for something else. It seems the values generated for the `AUTO_INCREMENT` columns are always increasing and consecutive but this is not true. **They are not always increasing and they are not always consecutive.** If you need to sort the rows in a non-trivial way then create a column for this specific purpose and write code to initialize and update it when needed. – axiac Jan 10 '17 at 17:53
  • @Uueerdo Thanks for this - I think using this method coupled with a new column called `last` this would probably be the best and most logical method. – Michael Emerson Jan 11 '17 at 09:23

0 Answers0