Bug #10393 about field type - auto_increment
Submitted: 5 May 2005 16:52 Modified: 6 Jun 2005 11:34
Reporter: wang mian Email Updates:
Status: No Feedback Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version: OS:Windows (windows 2000)
Assigned to: CPU Architecture:Any

[5 May 2005 16:52] 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 .
[6 May 2005 11:34] Hartmut Holzgraefe
What version of MySQL are you using? I can drop and recreate the primary key under 4.1.11 just fine ...
[6 Jun 2005 23:00] Bugs System
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".