Benjamin Pflugmann wrote:
> On Tue, Aug 10, 1999 at 03:05:40PM -0500, archiver@stripped wrote:
> > This message was sent from Geocrawler.com by "Brent Edwards"
> > Be sure to reply to that address.
> > I would like to create a multiple column primary key, and one of the columns be
> auto_increment. Here is my table create statements:
> > mysql> create table Problem (
> > -> Subscriber_ID numeric(7,0) NOT NULL,
> > -> Problem_ID numeric(7,0) NOT NULL AUTO_INCREMENT,
> > -> Request_Date date not null,
> > -> Completed_Date date,
> > -> Description text not null,
> > -> Status_ID numeric(7,0) NOT NULL,
> > -> primary key (Subscriber_ID, Problem_ID)
> > -> );
> > ERROR 1075: Incorrect table definition; There can only be one auto column and
> > must be defined as a key
> There must be a seperate key for the auto column Problem_ID, so add
> unique (Problem_ID)
> > This works fine if I remove the auto_increment from the Problem_ID column.
1) The Key doesn't have to be UNIQUE it also can be a 'normal' KEY.
2) If you change the order in your PRIMARY KEY definition it will work:
PRIMARY KEY (Problem_ID, Subscriber_ID)
So if you don't need this specific order for some query, then just use the 2) part.