Windows vs Red Hat4 PRIMARY KEY and AUTO_INCREMENT

I am trying to create a database on a XP PC using the create sql
script that works on a red Hat 4 mySQL database. This is the table:

CREATE table STEPSTATE2 (
STEPID int unsigned NOT NULL,
SCENARIOID int unsigned NOT NULL,
TIMESTAMP datetime NOT NULL,
ID smallint unsigned NOT NULL
AUTO_INCREMENT,
STATE varchar(20) NOT NULL,
PRIMARY KEY(STEPID, ID),
INDEX(STEPID),
CONSTRAINT stepid_stepstate_fkey
FOREIGN KEY (STEPID)
REFERENCES STEPS(STEPID) ON DELETE NO ACTION
);

The ID field is the second primary key that would allow the ID field
to be increment as follows:
123 3434 timestamp 1 waiting
123 3434 timestamp 2 running
123 3434 timestamp 3 completed
124 3434 timestamp 1 waiting
124 3434 timestamp 2 running
124 3434 timestamp 3 completed

However, when I tried to source the script on windows version of mySQL
it failed with the error:

ERROR 1075 (42000): Incorrect table definition; there can be only one
auto column and it must be defined as a key

The only way around it is to change the primary key to PRIMARY KEY(ID,
STEPID) which changes the ID behavior to:

123 3434 timestamp 1 waiting
123 3434 timestamp 3 running
123 3434 timestamp 4 completed
124 3434 timestamp 2 waiting
124 3434 timestamp 5 running
124 3434 timestamp 6 completed

Which is not what we want. I updated to mySQL alpha 6.0 and it still
gives me the error. If I export the Linux database using
mysqldump --user=$MYSQL_ACCOUNT --password=$MYSQL_PASSWORD terra >
$DUMP_FILE

and then import it using

mysql terra -u $MYSQL_ACCOUNT --password=$MYSQL_PASSWORD < $DUMP_FILE

The database appears to be created correctly without any errors. Does
anyone know why the windows version of mysql is not the same as the
red hat Linux version? Is there someway to get the table created as
it is on the Linux version without this error?

John T. Kerich wrote:
> I am trying to create a database on a XP PC using the create sql
> script that works on a red Hat 4 mySQL database. This is the table:
>
> CREATE table STEPSTATE2 (
> STEPID int unsigned NOT NULL,
> SCENARIOID int unsigned NOT NULL,
> TIMESTAMP datetime NOT NULL,
> ID smallint unsigned NOT NULL
> AUTO_INCREMENT,
> STATE varchar(20) NOT NULL,
> PRIMARY KEY(STEPID, ID),
> INDEX(STEPID),
> CONSTRAINT stepid_stepstate_fkey
> FOREIGN KEY (STEPID)
> REFERENCES STEPS(STEPID) ON DELETE NO ACTION
> );
>
> The ID field is the second primary key that would allow the ID field
> to be increment as follows:
> 123 3434 timestamp 1 waiting
> 123 3434 timestamp 2 running
> 123 3434 timestamp 3 completed
> 124 3434 timestamp 1 waiting
> 124 3434 timestamp 2 running
> 124 3434 timestamp 3 completed
>
> However, when I tried to source the script on windows version of mySQL
> it failed with the error:
>
> ERROR 1075 (42000): Incorrect table definition; there can be only one
> auto column and it must be defined as a key
>
> The only way around it is to change the primary key to PRIMARY KEY(ID,
> STEPID) which changes the ID behavior to:
>
> 123 3434 timestamp 1 waiting
> 123 3434 timestamp 3 running
> 123 3434 timestamp 4 completed
> 124 3434 timestamp 2 waiting
> 124 3434 timestamp 5 running
> 124 3434 timestamp 6 completed
>
> Which is not what we want. I updated to mySQL alpha 6.0 and it still
> gives me the error. If I export the Linux database using
> mysqldump --user=$MYSQL_ACCOUNT --password=$MYSQL_PASSWORD terra >
> $DUMP_FILE
>
> and then import it using
>
> mysql terra -u $MYSQL_ACCOUNT --password=$MYSQL_PASSWORD < $DUMP_FILE
>
> The database appears to be created correctly without any errors. Does
> anyone know why the windows version of mysql is not the same as the
> red hat Linux version? Is there someway to get the table created as
> it is on the Linux version without this error?
>

The message is correct.

But my question here is - the Primary Key is meant to uniquely identify
the row. That's what an autoincrement column does. The STEPID would
be redundant information, not required for the key.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================