yah, mysql only allows one auto increment field n that's used as the
primary key in tables. I don't think it has to be the primary key as
long as it is a unique key i think that's okay.
so u should be able to do : create table (myid int unsigned not null
auto_increment....., unique key (myid));
but this is effectively a primary key....
if u want some auto incrementing behavior but have it do so only on
certain scenarios and possibly hold null values, you can write an insert
trigger that would update the field on every insert.
On Mon, 2010-01-25 at 10:21 -0500, Yang Zhang wrote:
> In innodb, is it possible to have an auto_increment field without
> making it a (part of a) primary key? Why is this a requirement? I'm
> getting the following error. Thanks in advance.
> ERROR 1075 (42000): Incorrect table definition; there can be only one
> auto column and it must be defined as a key
> Yang Zhang