'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;
mysqlddl


Solution 1:[1]

My MySQL says "Incorrect table definition; there can be only one auto column and it must be defined as a key" So when I added primary key as below it started working:

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

Solution 2:[2]

Note also that "key" does not necessarily mean primary key. Something like this will work:

CREATE TABLE book (
    isbn             BIGINT NOT NULL PRIMARY KEY,
    id               INT    NOT NULL AUTO_INCREMENT,
    accepted_terms   BIT(1) NOT NULL,
    accepted_privacy BIT(1) NOT NULL,
    INDEX(id)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

This is a contrived example and probably not the best idea, but it can be very useful in certain cases.

Solution 3:[3]

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

Solution 4:[4]

This happens with you because in the case that you didn't create a primary key, SEQUELIZE will automatically generate a primary key with a default name id. Which causes a duplication because you already created one and SEQUELIZE created one too. But when you defined id as a primary key. SEQUELIZE didn't create the default id primary key.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 emstol
Solution 2 Matthew Read
Solution 3 Deept Raghav
Solution 4 Amr

Related Questions