Problem
In MySQL, what is the difference between MUL, PRI, and UNI?
I’m working on a MySQL query and have the following command:
desc mytable;
One of the fields is labeled as a MUL key, while the others are labeled as UNI or PRI.
I understand that if a key is PRI, it can only be associated with one record per table. Is it true that if a key is MUL, there could be several records connected with it?
Mytable’s response is as follows.
+-----------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+---------+------+-----+---------+-------+
| courseid | int(11) | YES | MUL | NULL | |
| dept | char(3) | YES | | NULL | |
| coursenum | char(4) | YES | | NULL | |
+-----------+---------+------+-----+---------+-------+
Asked by themaestro
Solution #1
DESCRIBE <table>;
Actually, this is a shortcut for:
SHOW COLUMNS FROM <table>;
In any instance, the “Key” attribute has three possible values:
The definitions of PRI and UNI are straightforward:
MUL, the third option (which you requested), is essentially an index that is neither a main key nor a unique key. Because many instances of the same value are permitted, the term originates from “multiple.” The following is taken directly from the MySQL documentation:
There is also a final caveat:
In general, the MySQL documentation is excellent. Check it out if you’re unsure!
Answered by robguinness
Solution #2
It denotes that the field is a non-unique index (part of). You have the option of issuing
show create table <table>;
To learn more about the table’s structure, click here.
Answered by Matt Healy
Solution #3
The following is taken from the MySQL 5.7 documentation:
This example’s control group has no PRI, MUL, or 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 MUL: MUL: MUL: MUL: MUL: MUL: MUL: MUL: MUL: MUL: MUL: MUL: MUL: 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 main key column is referred to as 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)
UNI: UNI: UNI: UNI: UNI: UNI: UNI: UNI: UNI: UNI: UNI: UNI: 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)
MUL is only on foo in a table with an index covering foo and bar:
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)
MUL exists for each of the two indexes on two columns in a table.
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)
MUL on the first column of a table with an Index covering three columns:
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)
MUL is a table with a foreign key that refers to the primary key of another table.
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)
Set the dial to “frappe” and stick it in your neocortex.
Answered by Eric Leschinski
Solution #4
This documentation for Mul was also useful to me: http://grokbase.com/t/mysql/mysql/9987k2ew41/key-field-mul-newbie-question
“MUL implies that the key enables the same value to appear in multiple rows. It isn’t a UNIQUE key, in other words.”
Let’s pretend you have two models: Post and Comment. Comment and Post have a has many relationship. 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 by committedandroider
Solution #5
UNI: For UNIQUE:
PRI: For PRIMARY:
MUL: For MULTIPLE:
Answered by iamfnizami
Post is based on https://stackoverflow.com/questions/5317889/sql-keys-mul-vs-pri-vs-uni