MySql SQL keys what is MUL vs PRI vs UNI

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
LVL 7
gudii9Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Pawan KumarDatabase ExpertCommented:
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

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
M. AhsanCommented:
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!
0
Pawan KumarDatabase ExpertCommented:
Question abandoned
Provided required solution
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.