[Warning: LOOOONG TEXT - enjoy ;-)]
Hi Victoria,
sorry for the misunderstanding. I just tried to reproduce the sample. While I
have records what I did I did not write down all commands exactly as they
are.
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)
Best regards
Nils Valentin
Tokyo/Japan
2003年 8月 6日 水曜日 19:22、Victoria Reznichenko さんは書きました:
> "Nils Valentin" <[EMAIL PROTECTED]> wrote:
> > Hi Victoria,
> >
> > Victoria Reznichenko wrote:
> >>"Nils Valentin" <[EMAIL PROTECTED]> wrote:
> >>>I have a problem understanding why MySQL is deleting a unique key
> >>> instead of a primary key.
> >>>
> >>>from Documentation: DROP PRIMARY KEY drops the primary index. If no such
> >>> index exists, it drops the first UNIQUE index in the table.
> >>>
> >>>When I do it then I get this:
> >>>
> >>>mysql> desc uksample4;
> >>>+-------+----------+------+-----+---------+-------+
> >>>
> >>>| 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 uksample4 drop primary key ;
> >>>Query OK, 0 rows affected (0.24 sec)
> >>>Records: 0 Duplicates: 0 Warnings: 0
> >>>
> >>>mysql> desc uksample4;
> >>>+-------+----------+------+-----+---------+-------+
> >>>
> >>>| 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)
> >>>
> >>>It deletes the unique key (id) instead of he primary key (tel).
> >>>
> >>>Did I do something wrong ?
> >>>
> >>>MySQL 4.0.14
> >>
> >>Your example worked fine for me. Could you provide a test case?
> >
> > I know that the example worked as decribed above, thank you for
> > confirming it. :-)
> > My Question is, why would it not drop the primary key, but the unique
> > key instead ??
> >
> > Which command would delete the primary key ?
>
> MySQL drops primary key for me. That is why I ask you about test case.
>
>
> --
> For technical support contracts, goto https://order.mysql.com/?ref=ensita
> This email is sponsored by Ensita.net http://www.ensita.net/
> __ ___ ___ ____ __
> / |/ /_ __/ __/ __ \/ / Victoria Reznichenko
> / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED]
> /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net
> <___/ www.mysql.com
--
---
Valentin Nils
Internet Technology
E-Mail: [EMAIL PROTECTED]
URL: http://www.knowd.co.jp
Personal URL: http://www.knowd.co.jp/staff/nils
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
