Update: 2003年 8月 7日 木曜日 17:06、Nils Valentin さんは書きました: > [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)
Reproducable on Redhat 8 and Redhat 9 Table types tested :MyISAM and Innodb > > 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 -- --- 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]