Error creating table using MySQL with InnoDB

Hello, I'm trying to create a  table with multiple Pks using MySQL 5.0.51a but I keep recieving this error: "#1075 - Incorrect table definition; there can be only one auto column and it must be defined as a key ". I'm using the engine InnoDB. As you can see there's only one auto increment field and is part of the primary key. How can I solve this? Thanks.
CREATE TABLE STATE(

CntCodCHAR( 3 ) NOT NULL ,
StaCod INT NOT NULL AUTO_INCREMENT ,
StaNameCHAR( 50 ) NOT NULL ,
PRIMARY KEY ( CntCod, StaCod )
) ENGINE = INNODB;

MySQL ha dicho: 

#1075 - Incorrect table definition; there can be only one auto column and it must be defined as a key

Open in new window

IvanGarceteAsked:
Who is Participating?
 
IvanGarceteConnect With a Mentor Author Commented:
Apparently the trick was to put the auto increment field first. Like this:
CREATE TABLE STATE(

CntCodCHAR( 3 ) NOT NULL ,
StaCod INT NOT NULL AUTO_INCREMENT ,
StaNameCHAR( 50 ) NOT NULL ,
PRIMARY KEY ( StaCod, CntCod )
) ENGINE = INNODB;

Open in new window

0
All Courses

From novice to tech pro — start learning today.