[SOLVED] alter table

[AV1611]

I can't work out the syntax of how to do this:

 

I need to alter an existing table.  I need to add an int column, and have it auto-increment it.

 

alter table tablename add column ID  int(5);

 

how do i make it autoincrement?

 

//EDIT

Is this right?

 

alter table tablename add column ID MEDIUMINT NOT NULL AUTO_INCREMENT;

[AV1611]

No, It has to be a key it gives an error

[AV1611]

OK,

 

The question becomes:

 

How would you add an auto incrementing column to an existing table as a key?

 

The table currently has no key.

 

 

[teng84]

ALTER TABLE table1 MODIFY col1 INT NOT NULL AUTO_INCREMENT

try something like that 

[AV1611]

That didn't work neither did this:

 

alter table rdqout add column id INT NOT NULL AUTO_INCREMENT, PRIMARY KEY(id);

 

You can't seem to set a field to AUTO_INCREMENT unless it's a KEY, and you can't set the KEY unless it's AUTO_INCREMENT...

 

Is this a limitation of MySQL?

 

I am dynamically creating the table, and need to add the ID field so i can do further manipulations.

[AV1611]

Got it!

 

alter table rdqout add column id int NOT NULL AUTO_INCREMENT, add PRIMARY KEY(id);

 

The comma needs to have add after it.  I didn't have the second add...