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;

回答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;

回答2:

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;

回答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

回答4:

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.

回答5:

If this error will occured in CodeIgniter:

$fields=array(
'slno' => array(
        'type' => 'int',
        'constraint' => 255,
        'unsigned' => TRUE,
        'auto_increment' => TRUE
        ),
'node_id' => array(
        'type' => 'int',
        'constraint' => 255,
        'unsigned' => TRUE
        ),
'node' => array(
        'type' => 'text',
        'null' => TRUE
        )

);

$this->dbforge->add_field($fields);

$this->dbforge->add_key("slno",TRUE);

$this->dbforge->create_table($key,TRUE);

Dont forget to add the primary key field....!