Alter column definition

[tobyheywood]

Just a quick question,

I have a number of tables all with an id column. Now like an idiot I forgot to specify the auto_increment option and now need to add it.

I have a few test records in place which I don't want to lose!

I know I could dump the entire database to a file using mysqldump and manually edit the file, then finally drop the db and recreate it using the altered mysqldump file, but this to me is a little extreme.

i've been trying to use the following to alter the field definition;

mysql> ALTER TABLE mn1 ALTER id id smallint unsigned not null primary key auto_increment;

and I receive the following;

ERROR 1064: You have an error in your SQL syntax near 'id smallint unsigned not null primary key auto_increment' at line 1

Could someone please point out what it is I'm doing wrong or tell me a better way?

Regards

Toby Heywood

 

[sleipnir214]

I think what you're looking for is something more along the lines of:

ALTER TABLE mn1 CHANGE COLUMN id smallint unsigned not null primary key auto_increment

For more information, please look here:

http://www.mysql.com/doc/en/ALTER_TABLE.html

By the way, are you aware of the sizes of numbers which can be stored in the various int column-types? smallint unsigned will only store a maximum of 65635 or 65536 ______________________________________________________________________

http://www.tuxedo.org/~esr/faqs/smart-questions.html

TANSTAAFL!

 

[tobyheywood]

Thank you for your prompt reply;

I tried your suggestion but it failed in exactly the same way. After a good amount of tinkering I found that the error that was being generated was caused by the fact that I did not specify the type of data type (ie Signed or Unsigned) once I specified that it worked fine.

As a rule of thumb I use

SMALLINT

for all id fields, should the table look as though it will reach the maximum unsigned value of 65535 then I shall alter the table to the

INT

data type!

Thanks again for your reply.

Regards Toby Heywood