Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

SQL keys, MUL vs PRI vs UNI

Tags:

sql

mysql

key

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.

Donate For Us

If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!