Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Can someone please tell me what's wrong with this table definition.
mysql version is 5.1.52-log

root@localhost spoolrdb> create table spoolqueue (
                             queue int,
                             idx bigint not null auto_increment,
                             status smallint,
                             querystring varchar(2048),
                             contenttype varchar(255),
                             characterencoding varchar(16),
                             body text,
                             primary key(queue,idx)
                             );
ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key
share|improve this question
    
I like this question (+1 for you) because it presents a MySQL gotcha which is unique to MyISAM. Asking about why is always much better than giving up and redesigning. –  RolandoMySQLDBA Jun 30 '11 at 15:12

2 Answers 2

up vote 7 down vote accepted

This will apparently work with MyISAM as storage engine, not InnoDB, if you can live with that.

Another way to get it to work is if you swap places of queue and idx in the primary key declaration.

share|improve this answer

Try removing the queue field from the primary key. You can index the queue column if you want

share|improve this answer
1  
That kind of defeats the point of the table. –  Nifle Jun 30 '11 at 8:08
2  
Do you realize that is what the asker is trying to avoid? –  jcolebrand Jun 30 '11 at 16:17

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.