Description:
Dropping primary key on InnoDB, when there is unique key, drops unique key..
MyISAM allows auto_incremnt as UNIQUE in CREATE, but not in ALTER.
How to repeat:
The problem(s) occured on Redhat 8 or 9 and Mysql server-max 4.0.13 (tar
format) from mysql homepage. I had to reinstall a machine to reproduce the
problem,this is why it took so long.
---- First the original problem
Redhat 9 + Mysql server-max 4.0.13 (tar format from mysql.com homepage)
create table uksample2 (id int unique not null ,name char(30),tel
char(20))type=innodb;
Query OK, 0 rows affected (0.01 sec)
mysql> alter table uksample2 add primary key (tel);
Query OK, 0 rows affected (0.04 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc uksample2;
+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id | int(11) | | UNI | 0 | |
| name | char(30) | YES | | NULL | |
| tel | char(20) | | PRI | | |
+-------+----------+------+-----+---------+-------+
3 rows in set (0.00 sec)
mysql> alter table uksample2 drop primary key;
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc uksample2;
+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id | int(11) | | | 0 | |
| name | char(30) | YES | | NULL | |
| tel | char(20) | | PRI | | |
+-------+----------+------+-----+---------+-------+
3 rows in set (0.00 sec)
Unique key was dropped. Why not Primary key, why no error message???
------
2nd example where it complains about that no two primary keys are
allowed,but lets me create them first ;-) This time on Redhat 8.
Redhat8 and 4.0.13 server-max (tar format from mysql.com homepage)
create table uksample2 (id int unique not null auto_increment,name
char(30),tel char(20));
Query OK, 0 rows affected (0.01 sec)
mysql> desc uksample2;
+-------+----------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+----------------+
| id | int(11) | | PRI | NULL | auto_increment |
| name | char(30) | YES | | NULL | |
| tel | char(20) | YES | | NULL | |
+-------+----------+------+-----+---------+----------------+
3 rows in set (0.00 sec)
mysql> alter table uksample2 add primary key (tel);
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc uksample2;
+-------+----------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+----------------+
| id | int(11) | | UNI | NULL | auto_increment |
| name | char(30) | YES | | NULL | |
| tel | char(20) | | PRI | | |
+-------+----------+------+-----+---------+----------------+
3 rows in set (0.00 sec)
mysql> alter table uksample2 drop primary key ;
ERROR 1075: Incorrect table definition; There can only be one auto column and
it must be defined as a key
mysql> alter table uksample2 drop primary key (id);
Note that also "alter table uksample2 drop index id;" will give me the same
error.
-----
On a Suse Linux system 8.1 with mysql server max 4.1 -alpha (rpm format from
mysql.com homepage) I get the below message, which I believe is correct.
mysql> create table uksample2 (id int unique not null auto_increment,name
char(30),tel char(20));
Query OK, 0 rows affected (0.07 sec)
mysql> alter table uksample2 add primary key (tel);
ERROR 1171: All parts of a PRIMARY KEY must be NOT NULL; If you need NULL in
a key, use UNIQUE instead
mysql> drop table uksample2;
Query OK, 0 rows affected (0.00 sec)