an error occurred while merging the default value and auto increment

Question

SQL query:

ALTER TABLE `x` CHANGE `y` `y` INT( 9 ) UNSIGNED NOT NULL DEFAULT '1000' AUTO_INCREMENT

MySQL said:

Documentation 1067 - Invalid default value for 'y' whether the default value can be combined with auto increment in mysql

What is the correct MySQL syntax?

Answer 1

This should work:

ALTER TABLE `x` CHANGE `y` `y` INT( 9 ) UNSIGNED AUTO_INCREMENT

You just can't define default value for auto_increment.

Answer 2

Sounds like what you want to do is set the starting value of the AUTO_INCREMENT. That you do by:

ALTER TABLE x AUTO_INCREMENT=1000