Incorrect table definition; there can be only one auto column and it must be defined as a key

What causes the Incorrect table definition; there can be only one auto column and it must be defined as a key?

When you work with MySQL, sometimes, you may get the Incorrect table definition; there can be only one auto column and it must be defined as a key error. This issue appears because you didn’t set a primary key. So, Sequelize will automatically create a primary key that is the same as the default name ID. That means there will be a duplication since both you and Sequelize generated one same field. On the other hand, if you define ID as a primary key, Sequelize won’t automatically generate anything. Thus, there won’t be any conflict that causes the error. In the blog today, we would like to introduce to you how to fix the issue in a few simple steps. Let’s explore now!

How to fix incorrect table definition; there can be only one auto column and it must be defined as a key

In order to solve this trouble, you just need to add a primary key to your code. Here is an example code when you don’t add a primary key to it and causes the error:

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;

The error will be addressed if you use the following code:

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;

Or you can try to add the 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;

Closing thoughts

All in all, with some small tips in the blog today, we hope that you can easily tackle the trouble. If you have another suggested solution, don’t forget to introduce it by leaving your comment below. Besides, after you try the method above and it works successfully, why don’t you share it with your friends? Thanks for your reading and hope you enjoy the blog today. See you the next time.