Create primary key times error: Error 1075 (42000): incorrect table definition; There can only one auto column and it mus

Reprint for self-study

ERROR 1075 (42000): incorrect table definition; There can is only one auto column and it must be defined as a key

Mysql> CREATE TABLE ANIMALS1 (

-> grp ENUM (' Fish ', ' mammal ', ' bird ') not NULL,

-> ID mediumint not NULL auto_increment,

-> name CHAR (not NULL),

-> PRIMARY KEY (grp,id)

->) Engine=innodb;

ERROR 1075 (42000): incorrect table definition; There can is only one auto column and it must be defined as a key

--there is no problem with the MyISAM storage engine

Mysql> CREATE TABLE ANIMALS1 (

-> grp ENUM (' Fish ', ' mammal ', ' bird ') not NULL,

-> ID mediumint not NULL auto_increment,

-> name CHAR (not NULL),

-> PRIMARY KEY (grp,id)

->) Engine=myisam;

Query OK, 0 rows Affected (0.00 sec)

--there is no problem creating a word Chini

mysql> CREATE TABLE Animals (

-> ID mediumint not NULL auto_increment,

-> name CHAR (not NULL),

-> PRIMARY KEY (ID)

->);

Query OK, 0 rows Affected (0.00 sec)

--View Creation statement

Mysql> Show CREATE TABLE animals\g;

1. Row ***************************

Table:animals

Create table:create Table ' animals ' (

' ID ' mediumint (9) Not NULL auto_increment,

' Name ' char (not NULL),

PRIMARY KEY (' id ')

) Engine=innodb auto_increment=11 DEFAULT charset=latin1

1 row in Set (0.00 sec)

ERROR:

No query specified

Solution:

Place the self-added column in the joint primary key on the leftmost side of the union main building, creating a successful

mysql> drop table animals1;

Mysql> CREATE TABLE ANIMALS1 (

-> grp ENUM (' Fish ', ' mammal ', ' bird ') not NULL,

-> ID mediumint not NULL auto_increment,

-> name CHAR (not NULL),

-> PRIMARY KEY (ID,GRP)

->);

Query OK, 0 rows Affected (0.00 sec)

Mysql> Show CREATE TABLE animals1\g;

1. Row ***************************

Table:animals1

Create table:create Table ' animals1 ' (

' GRP ' enum (' Fish ', ' mammal ', ' bird ') not NULL,

' ID ' mediumint (9) Not NULL auto_increment,

' Name ' char (not NULL),

PRIMARY KEY (' id ', ' grp ')

) Engine=innodb DEFAULT charset=latin1

1 row in Set (0.00 sec)

ERROR:

No query specified