I acquired a database from another developer. He didn't use auto_incrementers on any tables. They all have primary key ID's, but he did all the incrementing manually, in code.
Can I turn those into Auto_incrementers now?
For example, here's a table that has a primary key but is not
mysql> CREATE TABLE foo ( id INT NOT NULL, PRIMARY KEY (id) ); mysql> INSERT INTO foo VALUES (1), (2), (5);
MODIFY the column to redefine it with the
mysql> ALTER TABLE foo MODIFY COLUMN id INT NOT NULL AUTO_INCREMENT;
Verify this has taken effect:
mysql> SHOW CREATE TABLE foo;
CREATE TABLE foo ( `id` INT(11) NOT NULL AUTO_INCREMENT, PRIMARY KEY (`id`) ) ENGINE=MyISAM AUTO_INCREMENT=6 DEFAULT CHARSET=latin1
Note that you have modified the column definition in place, without requiring creating a second column and dropping the original column. The
PRIMARY KEY constraint is unaffected, and you don't need to mention in in the
ALTER TABLE statement.
Next you can test that an insert generates a new value:
mysql> INSERT INTO foo () VALUES (); -- yes this is legal syntax mysql> SELECT * FROM foo;
+----+ | id | +----+ | 1 | | 2 | | 5 | | 6 | +----+ 4 rows in set (0.00 sec)
I tested this on MySQL 5.0.51 on Mac OS X.
I also tested with
ENGINE=InnoDB and a dependent table. Modifying the
id column definition does not interrupt referential integrity.
To respond to the error 150 you mentioned in your comment, it's probably a conflict with the foreign key constraints. My apologies, after I tested it I thought it would work. Here are a couple of links that may help to diagnose the problem: