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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

[mysql] There can be only one auto column

Examples related to mysql

Examples related to ddl