List:General Discussion« Previous MessageNext Message »
From:Yong Lee Date:January 25 2010 9:08pm
Subject:Re: auto_increment without primary key in innodb?
View as plain text  
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.

Yong.

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
> http://www.mit.edu/~y_z/
> 

Thread
auto_increment without primary key in innodb?Yang Zhang25 Jan
  • RE: auto increment without primary key in innodb?Tom Worster25 Jan
    • Re: auto_increment without primary key in innodb?Yang Zhang25 Jan
      • Re: auto_increment without primary key in innodb?Jaime Crespo Rinc√≥n25 Jan
      • Re: auto_increment without primary key in innodb?Paul DuBois25 Jan
  • Re: auto_increment without primary key in innodb?Yong Lee26 Jan
    • Re: auto_increment without primary key in innodb?Johan De Meersman26 Jan