There can be only one auto column

mysql ddl
BuddyJoe picture BuddyJoe · Dec 27, 2011 · Viewed 169.3k times · Source

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;

Answer

emstol picture emstol · Dec 27, 2011

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;

Related questions

How to delete a column from a table in MySQL

Given the table created using: CREATE TABLE tbl_Country ( CountryId INT NOT NULL AUTO_INCREMENT, IsDeleted bit, PRIMARY KEY (CountryId) ) How can I delete the column IsDeleted?

Read More
Adding multiple columns AFTER a specific column in MySQL

I need to add multiple columns to a table but position the columns after a column called lastname. I have tried this: ALTER TABLE `users` ADD COLUMN ( `count` smallint(6) NOT NULL, `log` varchar(12) NOT NULL, `status` int(10) unsigned NOT NULL ) …

Read More
MySQL: ALTER TABLE if column not exists

I have this code: ALTER TABLE `settings` ADD COLUMN `multi_user` TINYINT(1) NOT NULL DEFAULT 1 And I want to alter this table only if this column doesn't exist. I'm trying a lot of different ways, but nothing works: ALTER TABLE `…

Read More