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

Accepted Answer

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!

Accepted Answer

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.

Answer 3

Must Read

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.

Sql Keys, Mul vs Pri vs Uni

6 Answers

Must Read

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

Maya Lin-Takahashi