Reputation: 1659

MySQL Difficult Question on auto_increment

I can't seem to figure out a MYSQL command or set of commands that will allow me to create an 'id' field, add auto_increment values, and then set it as the primary key.

The reason I want to do this is that I have an existing table with millions of rows and I want to assign a unique ID to each row which would become the primary key and have it auto_increment.

Is there a way to do this with SQL syntax or do I need to write a script?

Upvotes: 1

Answers (3)

Brian Driscoll

Reputation: 19635

This should be relatively simple:

ALTER TABLE `myTable` ADD `id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY

Upvotes: 4

Russell C.

Reputation: 1659

Just figured it out in case anyone else needs this in the future:

alter table test add column id int(9) unsigned auto_increment PRIMARY KEY;

Upvotes: 3

sahid

Reputation: 2610

May be you can create a new table like this:

CREATE TABLE new_table (id INT NOT NULL AUTO_INCREMENT,
   PRIMARY KEY (id), KEY(b))
ENGINE=InnoDB SELECT b,c FROM original;

Upvotes: 1

MySQL Difficult Question on auto_increment

Answers (3)

Related Questions