How to change id in existing MySQL tables with data to autoincrement from last highest id?

Question

I have just managed to migrate a pretty big database from SQL Server to MySQL. I didn't set the primary keys to auto increment during the migration because the tables have relationships based on ids as primary keys which are foreign keys in another table.

Now for adding new records I want to alter the primary key 'id' columns in all tables to be autoincrement but starting from the last highest number in the id column in each table.

What's the best way to do this without losing the relationships I already have?

UPDATE: Trying to add autoincrement gives me this error:

ERROR 1067: Invalid default value for 'id'

SQL Statement:

ALTER TABLE `skandium`.`brands` CHANGE COLUMN `id` `id` INT(11) NOT NULL DEFAULT '0' AUTO_INCREMENT

ERROR 1050: Table 'brands' already exists

SQL Statement:

CREATE TABLE `brands` (
  `id` int(11) NOT NULL DEFAULT '0',
  `brand` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

Answer 1

Just add the auto-increment on your needed columns. It won't affect existing rows. It will start with the next available value.

Note that a column being auto-increment means just that if you don't assign a value to it one will be added by MySQL. If you want to supply explicit values you can do so without anything special happening. So you could have set those columns to auto-increment right from the start.

A column with auto-increment cannot have an explicit default value.

Tested