0

Consider the following table:

create table t (
        n1 int, n2 int, index (n1, n2),
        u1 int, u2 int, unique index (u1, u2));

According to the SHOW COLUMNS documentation I was expecting Key = UNI for u1 because of

If Key is UNI, the column is the first column of a UNIQUE index. (A UNIQUE index permits multiple NULL values, but you can tell whether the column permits NULL by checking the Null field.)

What am I missing?

.

Output of desc t; (without the columns Default and Extra)

Field   Type    Null    Key     Default Extra
n1      int(11) YES     MUL
n2      int(11) YES
u1      int(11) YES     MUL
u2      int(11) YES

.

Output of show index from t; (without some irrelevant columns)

Table   Non_unique      Key_name Seq_in_index   Column_name     Cardinality
t   0           u1  1           u1          0
t   0           u1  2           u2          0
t   1           n1  1           n1          0
t   1           n1  2           n2          0
Micha Wiedenmann
  • 19,979
  • 21
  • 92
  • 137
  • I think your answer is here: http://stackoverflow.com/questions/5317889/sql-keys-mul-vs-pri – maqjav Jun 20 '13 at 10:56

1 Answers1

0

Updated to account for question update:

If multiple columns are involved in a unique index, the individual columns might show MUL rather than UNI because even though the two columns together must always be unique, the individual columns might not be.

From your link:

UNIQUE index may display as MUL if several columns form a composite UNIQUE index; although the combination of the columns is unique, each column can still hold multiple occurrences of a given value.

Matt Davies
  • 1,259
  • 10
  • 10
  • Ouch, I meant UNI, I have quoted the relevant part. I am sorry for changing my mind again. – Micha Wiedenmann Jun 20 '13 at 11:35
  • @Matt Davies - please can you edit your post to show the extract as a blockquote, rather than as code? Prefix the line with `>` rather than four spaces. I can't change this as the edit is deemed "too trivial". Thanks! – nurdglaw Jun 20 '13 at 11:39
  • Have updated based on your new question :) Noted @nurdglaw, thanks! – Matt Davies Jun 20 '13 at 15:27