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 | |
+-------+---------+------+-----+---------+-------+
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
