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]

Reply via email to