Bug #10394 about field type - auto_increment
Submitted: 5 May 2005 16:53 Modified: 5 May 2005 16:55
Reporter: wang mian Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Websites: bugs.mysql.com Severity:S3 (Non-critical)
Version: OS:Microsoft Windows (windows 2000)
Assigned to: CPU Architecture:Any

[5 May 2005 16:53] wang mian
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.
[5 May 2005 16:55] MySQL Verification Team
Of http://bugs.mysql.com/bug.php?id=10393