Mysql can't make column auto_increment

Laying the groundwork for AUTO_INCREMENT

Before implementing AUTO_INCREMENT, here's what you need to do:

Cleaning up existing IDs

Positive integers are the way to go. Eliminate any negative or zero value IDs:

// Let's give everyone a fresh start - flip negatives to positives and push zeros up one
UPDATE AllAboutIncrement SET ID = ABS(ID) + 1 WHERE ID <= 0;


Dealing with duplicates

Discard any duplicate, negative or zero value IDs:

// Let's purge all the negative vibes
DELETE FROM AllAboutIncrement WHERE ID < 0;

// Here's a little trick - ignore duplicates while adding a unique key
// Did I say trick? It's more like magic!
ALTER IGNORE TABLE AllAboutIncrement ADD UNIQUE (ID);


Pumping up the AUTO_INCREMENT value

Ensure the AUTO_INCREMENT value starts above your current maximum ID value:

// Setting AUTO_INCREMENT to 1000000 -- now that's a round number!
// Please don't do this if your highest ID isn't anywhere near that, or you'll have a long wait for new records!
ALTER TABLE AllAboutIncrement AUTO_INCREMENT = 1000000;


The quirks of AUTO_INCREMENT

Handle 'NO_AUTO_VALUE_ON_ZERO' mode

When modifying a table using ALTER TABLE with AUTO_INCREMENT, use 'NO_AUTO_VALUE_ON_ZERO' to stop 0 from resetting the counter:

// Don't worry zero, you're a hero in our book!
SET @@session.sql_mode = 'NO_AUTO_VALUE_ON_ZERO';
ALTER TABLE AllAboutIncrement ...
SET @@session.sql_mode = DEFAULT;


Taming SQL_MODE

You may need to adjust SQL_MODE temporarily - just remember to reset it afterward!

Pro tips for AUTO_INCREMENT

Beware reserved keywords

Avoid using reserved English keywords. Doing so will ensure your code doesn't throw up confetti... er, I mean errors:

// Because 'column_name' is so last season.
// Let's call it 'TheFieldOfIncrementingDoom' instead!
ALTER TABLE AllAboutIncrement MODIFY TheFieldOfIncrementingDoom INT AUTO_INCREMENT;


Ensure data integrity

Ensure your data integrity is fit as a fiddle before rocking the boat:

// Let's do a quick health check for our beloved schema
SELECT * FROM information_schema.tables WHERE table_schema = 'NameOfYourSchema';


Test all changes

After making changes, check that everything is functioning as expected by inserting new data:

// Making a sacrifice to the data gods here...
INSERT INTO AllAboutIncrement (YourColumnName) VALUES (NULL);


Turn error messages into guides

Don't fear error messages - think of them as your spirit guide through the wilderness of SQL troubleshooting.