primary key problem

[sundogro]

i have a table and i am trying to add an unique primary id field which is also autoincrement.the mysql query is like this:
"ALTER TABLE test1 ADD index_column SMALLINT not null AUTO_INCREMENT;"
i get a error" incorrect table definition:there can be only one auto field and it must be defined as a key"
what is the right query to solve this ?

[badman]

try this

ALTER TABLE test1 ADD index_col smallint primary key auto_increment;

this will work, provided you do not have any existing column as primary key.

cheers!

[[deleted]]

Better use an INTEGER instead of a smallint.

[mikeq]

That depends on the max number of records you think will be stored.

General rule is don't use a datatype that can store larger values than you need.

if you are going to have
4294967295 records (unsigned) then I agree use INT, but if you are never going to exceed 65535 records (unsigned) then SMALLINT is fine.

In addition to this performance is increased using smaller datatypes, disk reads are faster, less memory is used and indexing will use less resource.

See chapter 5.4.2 in MySQL documents

Mike