• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 988
  • Last Modified:

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
0
gudii9
Asked:
gudii9
  • 2
2 Solutions
 
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
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now