SQL keys, MUL vs PRI vs UNI

Question

DESCRIBE <table>; 

This is acutally a shortcut for:

SHOW COLUMNS FROM <table>;

In any case, there are three possible values for the "Key" attribute:

1. PRI
2. UNI
3. MUL

The meaning of PRI and UNI are quite clear:

• PRI => primary key
• UNI => 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 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.

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!

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".

For Mul, this was also helpful documentation to me - http://grokbase.com/t/mysql/mysql/9987k2ew41/key-field-mul-newbie-question

"MUL means that the key allows multiple rows to have the same value. That is, it's not a UNIque key."

For example, let's say you have two models, Post and Comment. Post has a has_many relationship with Comment. It would make sense then for the Comment table to have a MUL key(Post id) because many comments can be attributed to the same Post.

UNI: For UNIQUE:

• It is a set of one or more columns of a table to uniquely identify the record.
• A table can have multiple UNIQUE key.
• It is quite like primary key to allow unique values but can accept one null value which primary key does not.

PRI: For PRIMARY:

• It is also a set of one or more columns of a table to uniquely identify the record.
• A table can have only one PRIMARY key.
• It is quite like UNIQUE key to allow unique values but does not allow any null value.

MUL: For MULTIPLE:

• It is also a set of one or more columns of a table which does not identify the record uniquely.
• A table can have more than one MULTIPLE key.
• It can be created in table on index or foreign key adding, it does not allow null value.
• It allows duplicate entries in column.
• If we do not specify MUL column type then it is quite like a normal column but can allow null entries too hence; to restrict such entries we need to specify it.
• If we add indexes on column or add foreign key then automatically MUL key type added.