MySql SQL keys what is MUL vs PRI vs UNI

gudii9
gudii9 used Ask the Experts™
on
hi,

in mysql table shows table colums as MySql SQL keys what is MUL vs PRI vs UNI

what is difference between each. what is MUL is it same as foreign key? is it same across all databases?
please advise
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Database Expert
Awarded 2016
Top Expert 2016
Commented:
If you execute query SHOW COLUMNS FROM yourtableName you will get a column called Key whose value can be PRI,MUL,UNI.
 The meaning of PRI is primary Key and meaning of UNI - is unique index. The difference between primary key and unique key is that
 unique key can allow a single NULL value in the column and primary key cannot allow any NULL value in the column. This concept
 is same across all the DBs if i m not mistaken. MUL is different. Consider this as Index which is not Unique/Primary. Basically non unique
 index. Also note if any column's key is MUL then that column will be the first column of that index.
 
 You can read more from the documentation - https://dev.mysql.com/doc/refman/5.7/en/show-columns.html
 <<Key section>> from documentation -
 
 Whether the column is indexed:

If Key is empty, the column either is not indexed or is indexed only as a secondary column in a multiple-column, nonunique index.

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.

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.

A UNIQUE index may be displayed as PRI if it cannot contain NULL values and there is no PRIMARY KEY in the table. A UNIQUE index may display as MUL if several columns form a composite UNIQUE index; although the combination of the columns is unique, each column can still hold multiple occurrences of a given value. 

Open in new window

Commented:
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 occurences 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!
Pawan KumarDatabase Expert
Awarded 2016
Top Expert 2016

Commented:
Question abandoned
Provided required solution

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial