List:General Discussion« Previous MessageNext Message »
From:Paul DuBois Date:December 28 2002 4:58am
Subject:Re: Bug report: UNIQUE KEY and DESCRIBE TABLE
View as plain text  
At 16:44 -0800 12/27/02, Matt Solnit wrote:
>===============================================
>Bug report -- MySQL v4.06, binary distribution
>===============================================
>
>--------------
>Machine specs:
>--------------
>Compaq Presario desktop
>512 MB RAM
>Windows XP Professional SP1
>
>--------------------
>Problem description:
>--------------------
>MySQL does not return key information about any column after the first
>in a unique multi-column key.  Also, the "MUL" flag seems to indicate
>that the key is non-unique, when in fact it is.

1) Use SHOW KEYS if you want better information about the indexes on
a table.  DESCRIBE (aka SHOW COLUMNS) reports some information about
indexes, but that is not its primary purpose.

2) UNIQUE indexes can in fact hold non-unique values if any of the indexed
columns can be NULL.  (A UNIQUE index is allowed to store multiple NULL
values.)  But you are correct that the index in your particular table
can *not* be non-unique, because neither of the indexed columns can be
NULL.  (A further manifestation of this problem is that UNIQUE indexes
in BDB tables can *never* be non-unique, because BDB allows only one NULL
in a UNIQUE index, in contrast to other table types.)

>
>There is an equivalent symptom in the MySQL C API.  In the flags field
>of the MYSQL_FIELD structure returned by mysql_fetch_field(), the
>MULTIPLE_KEY_FLAG will only be present in the first column.
>
>-------------
>Test script:
>-------------
>mysql>USE test
>mysql>CREATE TABLE mytable (a INT NOT NULL, b INT NOT NULL, c INT NOT
>NULL, d INT NOT NULL, PRIMARY KEY (a), UNIQUE KEY (b, c));
>mysql>DESCRIBE TABLE mytable;
>
>------------------
>Results:
>------------------
>+-------+---------+------+-----+---------+-------+
>| Field | Type    | Null | Key | Default | Extra |
>+-------+---------+------+-----+---------+-------+
>| a     | int(11) |      | PRI | 0       |       |
>| b     | int(11) |      | MUL | 0       |       |
>| c     | int(11) |      |     | 0       |       |
>| d     | int(11) |      |     | 0       |       |
>+-------+---------+------+-----+---------+-------+

Thread
Bug report: UNIQUE KEY and DESCRIBE TABLEMatt Solnit28 Dec
  • Re: Bug report: UNIQUE KEY and DESCRIBE TABLEHeikki Tuuri29 Dec
Re: Bug report: UNIQUE KEY and DESCRIBE TABLEPaul DuBois28 Dec