Description:
mysql> create table temp (id int not null auto_increment,rem int not null primar
y key,rem2 int not null,unique index1 (id));
Query OK, 0 rows affected (0.03 sec)
mysql> show fields from temp;
+-------+---------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+----------------+
| id | int(11) | | UNI | NULL | auto_increment |
| rem | int(11) | | PRI | 0 | |
| rem2 | int(11) | | | 0 | |
+-------+---------+------+-----+---------+----------------+
3 rows in set (0.02 sec)
mysql> show index from temp;
+-------+------------+----------+--------------+-------------+-----------+------
-------+----------+--------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardi
nality | Sub_part | Packed | Comment |
+-------+------------+----------+--------------+-------------+-----------+------
-------+----------+--------+---------+
| temp | 0 | PRIMARY | 1 | rem | A |
0 | NULL | NULL | |
| temp | 0 | index1 | 1 | id | A |
0 | NULL | NULL | |
+-------+------------+----------+--------------+-------------+-----------+------
-------+----------+--------+---------+
2 rows in set (0.00 sec)
mysql> alter table temp drop primary key;
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show fields from temp;
+-------+---------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+----------------+
| id | int(11) | | PRI | NULL | auto_increment |
| rem | int(11) | | | 0 | |
| rem2 | int(11) | | | 0 | |
+-------+---------+------+-----+---------+----------------+
3 rows in set (0.01 sec)
mysql> show index from temp;
+-------+------------+----------+--------------+-------------+-----------+------
-------+----------+--------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardi
nality | Sub_part | Packed | Comment |
+-------+------------+----------+--------------+-------------+-----------+------
-------+----------+--------+---------+
| temp | 0 | index1 | 1 | id | A |
0 | NULL | NULL | |
+-------+------------+----------+--------------+-------------+-----------+------
-------+----------+--------+---------+
1 row in set (0.00 sec)
How to repeat:
field id is unique key & field rem is primary key , run sql :
alter table temp drop primary key;
the field id change to PRI whit 'show fields from temp' , why not UNI ?
| id | int(11) | | PRI | NULL | auto_increment |
I can't create new primary key .
sorry , my english too bad.
建立一个带有auto_increment类型字段的表后，在没有其他主键的情况下，该自增字段自动被定义为主键，因为不允许重复主键所以无法再建立新的主键，虽然使用show index命令查看到的索引类型还是唯一索引。而在此之后如果建立新的唯一键，使用show fields命令会看到新的唯一键带有PRI标记，而使用show index命令则显示为唯一键，这种情况下我无法通过查询字段信息来确定该键到底是UNI还是PRI，而如果仅通过查询索引信息会导致无法正常的建立主键而引起程序错误。
Description: mysql> create table temp (id int not null auto_increment,rem int not null primar y key,rem2 int not null,unique index1 (id)); Query OK, 0 rows affected (0.03 sec) mysql> show fields from temp; +-------+---------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+---------+------+-----+---------+----------------+ | id | int(11) | | UNI | NULL | auto_increment | | rem | int(11) | | PRI | 0 | | | rem2 | int(11) | | | 0 | | +-------+---------+------+-----+---------+----------------+ 3 rows in set (0.02 sec) mysql> show index from temp; +-------+------------+----------+--------------+-------------+-----------+------ -------+----------+--------+---------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardi nality | Sub_part | Packed | Comment | +-------+------------+----------+--------------+-------------+-----------+------ -------+----------+--------+---------+ | temp | 0 | PRIMARY | 1 | rem | A | 0 | NULL | NULL | | | temp | 0 | index1 | 1 | id | A | 0 | NULL | NULL | | +-------+------------+----------+--------------+-------------+-----------+------ -------+----------+--------+---------+ 2 rows in set (0.00 sec) mysql> alter table temp drop primary key; Query OK, 0 rows affected (0.03 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> show fields from temp; +-------+---------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+---------+------+-----+---------+----------------+ | id | int(11) | | PRI | NULL | auto_increment | | rem | int(11) | | | 0 | | | rem2 | int(11) | | | 0 | | +-------+---------+------+-----+---------+----------------+ 3 rows in set (0.01 sec) mysql> show index from temp; +-------+------------+----------+--------------+-------------+-----------+------ -------+----------+--------+---------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardi nality | Sub_part | Packed | Comment | +-------+------------+----------+--------------+-------------+-----------+------ -------+----------+--------+---------+ | temp | 0 | index1 | 1 | id | A | 0 | NULL | NULL | | +-------+------------+----------+--------------+-------------+-----------+------ -------+----------+--------+---------+ 1 row in set (0.00 sec) How to repeat: field id is unique key & field rem is primary key , run sql : alter table temp drop primary key; the field id change to PRI whit 'show fields from temp' , why not UNI ? | id | int(11) | | PRI | NULL | auto_increment | I can't create new primary key . sorry , my english too bad. 建立一个带有auto_increment类型字段的表后，在没有其他主键的情况下，该自增字段自动被定义为主键，因为不允许重复主键所以无法再建立新的主键，虽然使用show index命令查看到的索引类型还是唯一索引。而在此之后如果建立新的唯一键，使用show fields命令会看到新的唯一键带有PRI标记，而使用show index命令则显示为唯一键，这种情况下我无法通过查询字段信息来确定该键到底是UNI还是PRI，而如果仅通过查询索引信息会导致无法正常的建立主键而引起程序错误。