SQL keys, MUL vs PRI vs UNI

by 
DESCRIBE <table>;

This is acutally a shortcut for:

SHOW COLUMNS FROM <table>;

In any case, there are three possible values for the “Key” attribute:

  1. PRI
  2. UNI
  3. 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 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.

As a general note, the MySQL documentation is quite good. When in doubt, check it out!

More Related Contents:

  1. Recreating a query using INNER JOIN? [duplicate]
  2. Create a Cumulative Sum Column in MySQL
  3. How to perform grouped ranking in MySQL
  4. Equivalent of explode() to work with strings in MySQL
  5. MySQL Trigger after update only if row has changed
  6. MySQL “WITH” clause
  7. mysql select dynamic row values as column names, another column as value
  8. Ordering by specific field value first
  9. MySQL “Group By” and “Order By”
  10. MySQL DELETE FROM with subquery as condition
  11. MySQL Query to select data from last week?
  12. SQL – IF EXISTS UPDATE ELSE INSERT INTO
  13. SELECT / GROUP BY – segments of time (10 seconds, 30 seconds, etc)
  14. Simple Random Samples from a Sql database
  15. Are there disadvantages to using a generic varchar(255) for all text-based fields?
  16. How do I use properly CASE..WHEN in MySQL
  17. MySQL “IN” operator performance on (large?) number of values
  18. How to get the latest record in each group using GROUP BY? [duplicate]
  19. How do I UPDATE a row in a table or INSERT it if it doesn’t exist?
  20. SQL unique varchar case sensitivity question
  21. How do I count columns of a table
  22. Difference between two table structure
  23. Difference between SET autocommit=1 and START TRANSACTION in mysql (Have I missed something?)
  24. MySQL equivalent of Oracle’s SEQUENCE.NEXTVAL
  25. Why MySQL is giving error “Not allowed to return a result set from a function”?
  26. When to use a left outer join?
  27. MySQL 5.6 – DENSE_RANK like functionality without Order By
  28. MySQL OPTIMIZE all tables?
  29. Strip last two characters of a column in MySQL
  30. Deleting a row based on the max value

Leave a Comment