Back

Explore Courses Blog Tutorials Interview Questions
0 votes
2 views
in SQL by (6.1k points)
edited by

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

1 Answer

0 votes
by (12.7k points)
edited by
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 PRIUNIMUL.

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

Related questions

0 votes
1 answer
asked Jul 23, 2019 in SQL by Tech4ever (20.3k points)
0 votes
1 answer
asked Jul 13, 2019 in SQL by Tech4ever (20.3k points)
0 votes
1 answer
asked Jul 6, 2019 in SQL by Tech4ever (20.3k points)
+3 votes
1 answer
asked Jul 3, 2019 in SQL by Tech4ever (20.3k points)
+3 votes
1 answer
asked Jul 3, 2019 in SQL by Tech4ever (20.3k points)

Browse Categories

...