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/ > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org