SQL keys, MUL vs PRI vs UNI

Question

What is the difference between MUL, PRI and UNI in MySQL?

I'm working on a MySQL query, using the command:

desc mytable;

One of the fields is shown as being a MUL key, others show up as UNI or PRI.

I know that if a key is PRI, only one record per table can be associated with that key. If a key is MUL, does that mean that there could be more than one associated record?

Here's the response of mytable.

+-----------+---------+------+-----+---------+-------+
| Field     | Type    | Null | Key | Default | Extra |
+-----------+---------+------+-----+---------+-------+
| courseid  | int(11) | YES  | MUL | NULL    |       | 
| dept      | char(3) | YES  |     | NULL    |       | 
| coursenum | char(4) | YES  |     | NULL    |       | 
+-----------+---------+------+-----+---------+-------+

score 532 · Answer 1 · edited Sep 05 '19 at 08:37

532

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 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!

edited Sep 05 '19 at 08:37

Yves M.

29,855
23
108
144

answered Mar 07 '13 at 10:36

robguinness

16,266
14
55
65

6

"Primary keys must contain unique values." http://www.w3schools.com/sql/sql_primarykey.asp – ktm5124 Mar 25 '13 at 00:58
13

Is it possible to say, in some contexts, that MUL means the key is a foreign key?? – Armel Larcier Jul 05 '13 at 13:56
7

@robguinness, MySQL documentation reads like it's written by non-English. Many times they would take 3 lines to explain something that could be done with 1 line. – Pacerier Feb 12 '15 at 10:23
31

Also Note that a table with [a **foreign** key that references another table's primary key](http://stackoverflow.com/a/25101478/632951) is `MUL`. – Pacerier Feb 12 '15 at 10:27
4

@pacerier, I agree with you regarding the verboseness of MySQL documentation. That's why Stackoverflow is usually the first place I check out, especially if I'm in a hurry. ;-) – robguinness May 12 '18 at 04:32
1

Why do some people write and some people send these strange explanations from the official documentation written for extraterrestrials? Could anybody just give a straight answer is `MUL` a `foreign` key or not? – t7e Sep 08 '20 at 19:08
2

The docs and explanations are written assuming a baseline of knowledge necessary to use the language. Consider reading further about any confusing terms - try to learn the underlying concepts, and you'll have an easier time understanding the docs. No one can answer for you whether any given column with `MUL` is a foreign key or not. It might be, or it might have a non-unique index for some other reason. – AjimOthy Sep 10 '20 at 03:20
@Pacerier I think it written that way because its also read by non english readers. – Amit Kaushik Apr 05 '23 at 22:22
1

@t7e Let me be simple: **MUL does *NOT* equal foregin key ; `MUL !== FK`** don't rely on that, if you want to find if column is a foreign key it's more complicated and you would have to check `INFORMATION_SCHEMA.KEY_COLUMN_USAGE` – jave.web May 15 '23 at 11:26

score 200 · Accepted Answer · answered Mar 15 '11 at 21:26

200

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.

answered Mar 15 '11 at 21:26

Matt Healy

18,033
4
56
56

3

If that field is (part of) a non-unique index, then why does `MUL` show against only that column and not all other columns as well.? – Coder Jun 15 '19 at 23:23
3

There aren't necessarily any other columns involved. Non-unique just means the same value can occur more than once _in that column_. – pgoetz Aug 07 '19 at 16:07
robguinness' answer is the most direct answer to the question, this answer is the most helpful in terms of helping you get all the info you need. – Jemar Jones Jul 26 '23 at 15:11

score 114 · Answer 3 · edited Aug 19 '17 at 17:01

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

score 11 · Answer 4 · answered Sep 07 '15 at 19:48

11

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.

answered Sep 07 '15 at 19:48

committedandroider

8,711
14
71
126

7

if it's just not a UNIQUE key, why explicitly mention as MUL? By default it's not UNIQUE anyway, isn't it? or am I missing something? – Sudip Bhandari Aug 10 '16 at 07:46
1

@SudipBhandari if you set the index on a field which neither primary nor unique then MySQL sets the key type as MUL, addition to above explanation, such kind of types helps MySQL to understand what kind of index it is dealing with. – Adnan Oct 12 '18 at 23:13

score 2 · Answer 5 · edited Jan 28 '21 at 18:00

Let's understand in simple words

PRI - It's a primary key, and used to identify records uniquely.
UNI - It's a unique key, and also used to identify records uniquely. It looks similar like primary key but a table can have multiple unique keys and unique key can have one null value, on the other hand table can have only one primary key and can't store null as a primary key.
MUL - It's doesn't have unique constraint and table can have multiple MUL columns.

Note: These keys have more depth as a concept but this is good to start.

score 1 · Answer 6 · answered Oct 01 '20 at 09:41

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.

score 0 · Answer 7 · answered Mar 24 '23 at 19:03

A PRI key is the PRIMARY KEY of the table. Each row is required to have at least, at most one and it is not repeated in the table. A UNI key signifies that any value of the specified row exists at most once in the table. A MUL key signifies that the records could have a value of the specified row multiple types in the table or sometimes none.

SQL keys, MUL vs PRI vs UNI

7 Answers7

Walkthough on what is MUL, PRI and UNI in MySQL?

Live Examples

Linked

Related