From: Johan De Meersman Date: January 26 2010 11:04am Subject: Re: auto_increment without primary key in innodb? List-Archive: http://lists.mysql.com/mysql/220394 Message-Id: MIME-Version: 1.0 Content-Type: multipart/alternative; boundary=0016e64ea9226a4d8c047e0f3fcb --0016e64ea9226a4d8c047e0f3fcb Content-Type: text/plain; charset=ISO-8859-1 On Mon, Jan 25, 2010 at 10:08 PM, Yong Lee wrote: > 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.... > Only mostly true :-) It *is* the same for MyISAM, but for InnoDB the primary key is special, as that is the one that stores the data inline (clustered index). Additional unique keys will only contain a reference to the primary key value for the record. -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel --0016e64ea9226a4d8c047e0f3fcb--