Reset MySQL auto_increment when a large number already exists?

Question

I have a table with an auto incrementing column. I need to set one of the rows to 1000 and never touch it again, but now that I've set it to 1000, auto increment keeps starting at 1001 and refuses to start at 1. Is there any way to get around this?

Answer 1

You cannot:

To change the value of the AUTO_INCREMENT counter to be used for new rows, do this:

ALTER TABLE t2 AUTO_INCREMENT = value;

You cannot reset the counter to a value less than or equal to any that have already been used. For MyISAM, if the value is less than or equal to the maximum value currently in the AUTO_INCREMENT column, the value is reset to the current maximum plus one. For InnoDB, if the value is less than the current maximum value in the column, no error occurs and the current sequence value is not changed.

http://dev.mysql.com/doc/refman/5.1/en/alter-table.html

Answer 2

Simple and short answer: you can't do this.

If you could, what would happen if you start your auto-increment at 1 and insert 1000 rows? The last couldn't be inserted due to "duplicate key"-error.

If you have to have a predefinded entry, with an id that never changes and is easy to remember, why don't you use 0 for that? Or, if you really need to use 1000, what's the problem with letting the other columns start at 1001?