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