SQL keys, MUL vs PRI vs UNI
It means that the field is (part of) a non-unique index. You can issue
show create table <table>;
To see more information about the table structure.
Walkthough on what is MUL, PRI and UNI in MySQL?
From the MySQL 5.7 documentation:
- If Key is PRI, the column is a PRIMARY KEY or is one of the columns in a multiple-column PRIMARY KEY.
- 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.)
- If Key is MUL, the column is the first column of a nonunique index in which multiple occurrences of a given value are permitted within the column.
Live Examples
Control group, this example has neither PRI, MUL, nor UNI:
mysql> create table penguins (foo INT);
Query OK, 0 rows affected (0.01 sec)
mysql> desc penguins;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| foo | int(11) | YES | | NULL | |
+-------+---------+------+-----+---------+-------+
1 row in set (0.00 sec)
A table with one column and an index on the one column has a MUL:
mysql> create table penguins (foo INT, index(foo));
Query OK, 0 rows affected (0.01 sec)
mysql> desc penguins;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| foo | int(11) | YES | MUL | NULL | |
+-------+---------+------+-----+---------+-------+
1 row in set (0.00 sec)
A table with a column that is a primary key has PRI
mysql> create table penguins (foo INT primary key);
Query OK, 0 rows affected (0.02 sec)
mysql> desc penguins;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| foo | int(11) | NO | PRI | NULL | |
+-------+---------+------+-----+---------+-------+
1 row in set (0.00 sec)
A table with a column that is a unique key has UNI:
mysql> create table penguins (foo INT unique);
Query OK, 0 rows affected (0.01 sec)
mysql> desc penguins;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| foo | int(11) | YES | UNI | NULL | |
+-------+---------+------+-----+---------+-------+
1 row in set (0.00 sec)
A table with an index covering foo and bar has MUL only on foo:
mysql> create table penguins (foo INT, bar INT, index(foo, bar));
Query OK, 0 rows affected (0.01 sec)
mysql> desc penguins;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| foo | int(11) | YES | MUL | NULL | |
| bar | int(11) | YES | | NULL | |
+-------+---------+------+-----+---------+-------+
2 rows in set (0.00 sec)
A table with two separate indexes on two columns has MUL for each one
mysql> create table penguins (foo INT, bar int, index(foo), index(bar));
Query OK, 0 rows affected (0.01 sec)
mysql> desc penguins;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| foo | int(11) | YES | MUL | NULL | |
| bar | int(11) | YES | MUL | NULL | |
+-------+---------+------+-----+---------+-------+
2 rows in set (0.00 sec)
A table with an Index spanning three columns has MUL on the first:
mysql> create table penguins (foo INT,
bar INT,
baz INT,
INDEX name (foo, bar, baz));
Query OK, 0 rows affected (0.01 sec)
mysql> desc penguins;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| foo | int(11) | YES | MUL | NULL | |
| bar | int(11) | YES | | NULL | |
| baz | int(11) | YES | | NULL | |
+-------+---------+------+-----+---------+-------+
3 rows in set (0.00 sec)
A table with a foreign key that references another table's primary key is MUL
mysql> create table penguins(id int primary key);
Query OK, 0 rows affected (0.01 sec)
mysql> create table skipper(id int, foreign key(id) references penguins(id));
Query OK, 0 rows affected (0.01 sec)
mysql> desc skipper;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id | int(11) | YES | MUL | NULL | |
+-------+---------+------+-----+---------+-------+
1 row in set (0.00 sec)
mysql> desc penguins;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
+-------+---------+------+-----+---------+-------+
1 row in set (0.00 sec)
Stick that in your neocortex and set the dial to "frappe".
DESCRIBE <table>;
This is acutally a shortcut for:
SHOW COLUMNS FROM <table>;
In any case, there are three possible values for the "Key" attribute:
PRI
UNI
MUL
The meaning of PRI
and UNI
are quite clear:
PRI
=> primary keyUNI
=> unique key
The third possibility, MUL
, (which you asked about) is basically an index that is neither a primary key nor a unique key. The name comes from "multiple" because multiple occurrences of the same value are allowed. Straight from the MySQL documentation:
If
Key
isMUL
, the column is the first column of a nonunique index in which multiple occurrences of a given value are permitted within the column.
There is also a final caveat:
If more than one of the Key values applies to a given column of a table, Key displays the one with the highest priority, in the order
PRI
,UNI
,MUL
.
As a general note, the MySQL documentation is quite good. When in doubt, check it out!