There can be only one auto column

Question

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;

score 174 · Answer 1 · answered Dec 27 '11 at 14:53

174

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;

answered Dec 27 '11 at 14:53

emstol

5,966
6
27
32

11

What is the correct course of action if the column is part of a composite key? – Nubcake Sep 20 '17 at 18:13
2

What is the syntax when altering the table? – Mike Harrison Mar 01 '19 at 20:00
4

@MikeHarrison looks like you can just put `ALTER TABLE book ADD id INT AUTO_INCREMENT NOT NULL, ADD PRIMARY KEY (id);` – suxur Jul 25 '19 at 16:17

score 63 · Accepted Answer · answered Dec 27 '11 at 14:49

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;

score 25 · Answer 3 · answered Jan 11 '18 at 00:35

25

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.

answered Jan 11 '18 at 00:35

Matthew Read

1,365
1
30
50

2

This helped me in a situation where I wanted to define a composite key for easy updating but I also wanted to have an auto-incrementing ID for debugging purposes. Is there anything I should be aware of in terms of risk, apart from slightly slower writes possibly? – Mattias Martens Mar 14 '19 at 02:18
3

@Mattias No, I don't believe there's any particular risk. This is intentionally supported, with the auto column being the primary key purely by convention (and for simplicity). – Matthew Read Mar 15 '19 at 03:52
1

You can also use UNIQUE key, or any other key listed here - https://www.c-sharpcorner.com/UploadFile/65fc13/working-with-keys-in-mysql/#:~:text=In%20MySQL%2C%20a%20key%20is,from%20a%20table%20as%20needed. – Sahil Singh May 25 '21 at 20:18
This gives me "Multiple primary key defined" – AlxVallejo Mar 08 '22 at 17:11
Unlikely, since there's only one primary key. Works just fine here on 5.5, 5.6, 5.7, and 8.0 @AlxVallejo: https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=da0a4b0797a16f3ff0368d6a68f5ab30 – Matthew Read Mar 08 '22 at 22:56

score 6 · Answer 4 · answered Dec 27 '11 at 14:49

6

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

answered Dec 27 '11 at 14:49

Deept Raghav

1,430
13
14

score 0 · Answer 5 · answered Feb 10 '22 at 03:07

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.

score 0 · Answer 6 · answered Nov 24 '22 at 18:16

I want to add a solution that helped me. In case there is a multi-column primary key, the following would generate the same error:

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

But the following works fine (=note the ordering of the PRIMARY KEY):

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

There can be only one auto column

6 Answers6

Linked

Related