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

Answer 1

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!