SQL keys, MUL vs PRI vs UNI

Question

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

I am working on a MySQL query, executing the command:

desc mytable; 

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

I also knew that if a key is PRI, only one record per table could be associated with that key. If a key is MUL, is itt mean that there could be more than one associated records?

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 actually a shortcut for:

SHOW COLUMNS FROM <table>;

In any of the circumstances, there could be three possible values for the "Key" attribute: 

1. PRI
2. UNI
3. MUL

The application of the PRI and UNI are pretty clear:

• PRI => primary key
• UNI => unique key

The third possibility is that MUL, (which you were asked about) is basically an index that is neither a primary key nor a unique key. The name actually 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.

If you want to learn more about SQL, Check out this SQL Certification by Intellipaat.

Answer 2

The primary difference between the ‘MUL’,’PRI’, and ‘UNI’ keys in MYSQL are: 

PRI → Primary Key

• Only one primary is allowed per table and it cannot contain NULL values.

• A Primary key can consist of multiple columns

• If there is a primary key, then it ensures that no two records in the table can have the same value for that key

UNI → Unique Key 

• Multiple unique keys can exist per table.

• A unique key constraint ensures that all values in the column are unique/distinct

• A unique key can have one NULL value 

• A unique key can consist of multiple columns

MUL → Multiple Key

• Multiple ‘MUL’ keys can exist per table

• The MUL key is typically used for foreign keys or other columns that are often queried but can have duplicate values.

• Allows duplicate values in indexed column(s), In other words, this indicates that the column is indexed, but the index is not unique.

In your example,

‘courseid’ is ‘MUL’ key which means:

• Duplicate values are allowed in the ‘courseid’ column

• This index is likely used to optimize queries that frequently filter by ‘courseid’

• ‘dept’ and ‘coursenum’ are not indexed, as indicated by the blank in the ‘Key’ column.

Answer 3

The main distinction among the 'MUL', 'PRI', and 'UNI' keys in MYSQL is:

PRI is an abbreviation for Primary Key.

One primary key is permitted per table and it must not have any NULL values.

A Primary key may be composed of several columns.

Having a primary key prevents duplicate values in a table.

UNI → Key that is unique

Several distinct keys may coexist within a single table.

A special key restriction guarantees that every value in the column is different/unique.

A sole key is capable of containing a NULL value.

A combination of columns can form a distinct key.

MUL stands for Multiple Key.

Several 'MUL' keys may be present within a single table.

The MUL key is commonly used for foreign keys or other columns that are frequently queried despite potentially having duplicate values.

In the example you provided,

The 'courseid' key is designated as 'MUL', indicating:

The 'courseid' column allows for duplicate values.

This index is probably utilized to enhance queries that often involve filtering by 'courseid'

"Dept" and "coursenum" do not have indexes, as shown by the empty space in the "Key" column.