Search code examples
mysqlddl

There can be only one auto column

How do I correct the error from MySQL 'you can only have one auto increment column'.

CREATE TABLE book (
   id INT AUTO_INCREMENT NOT NULL,
   accepted_terms BIT(1) NOT NULL,
   accepted_privacy BIT(1) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Solution

  • The full error message sounds:

    ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key

    So add primary key to the auto_increment field:

    CREATE TABLE book (
   id INT AUTO_INCREMENT primary key NOT NULL,
   accepted_terms BIT(1) NOT NULL,
   accepted_privacy BIT(1) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;