9

Possible Duplicate:
Advantages and Disadvantages to using ENUM vs Integer types?

Considering MySQL only...

I'm curious to know what the community thinks the proper use-case for an ENUM type vs. a FK to a types table.

I've read this question: Advantages and Disadvantages to using ENUM vs Integer types?, however the use-cases aren't really covered well enough for me.

Improve this question
3
  • 2
    Perhaps then you should open a bounty on that question and specify where you would like to see more information.
    – Nick Chammas
    Commented Jan 12, 2012 at 18:57
  • 1
    @Randy - concerning Nick's suggestion, you can now add a reason when you add a bounty laying out what use-cases you want covered
    – Jack Douglas
    Commented Jan 12, 2012 at 20:40
  • 2
    IMHO this question is not a duplicate. The old question is simply pros-and-cons of ENUM. This question is more along the lines of using ENUM versus strict implementing of type tables via foreign key. The way I answered each question clearly reflects this. If there is no other objection, I'd like to vote this one to be reopened.
    – RolandoMySQLDBA
    Commented Jan 13, 2012 at 5:48

2 Answers 2

Reset to default
5

When using ENUM to have a range of values, you have to properly plan what values you will use. Once those values are put in place, it can be rather challenging to perform any kind of simple maintenance should you want to redefine the ENUM range.

While there are bait-and-switch tricks you can perform to extend a range for an ENUM, those same tricks become very cumbersome (maybe even impossible) if you want to do other things such as reducing or altering an ENUM range.

Using foreign keys makes a great deal of sense when you know that the types you are establishing can be extended, reduced, or altered. Just by adding new entries into a table with the types needed, keys can be passed around to other tables without worrying about the underlying value being properly represented in the foreign tables. In addition, you would not be worried about those values matching up exactly with the base table definition of the type. Such concerrns would definitely apply if using ENUM.

Foreign Key Constraints would server to protect a row from absorbing invalid types. Hence, this would not be suitable for ENUMs because the constraint checking would have to be done in your application rather than in the database.

Here is what should be considered about ENUM usage:

ENUMs work best when

  • the type you are representing is local to the table only
  • it must represent a type that is never changed
  • it will never likely experience redefinition
  • its cardinality needs to be low
  • its portability must always be based on logcial dumps of the table, never physical. Otherwise, bait-and-switch methods of maintenance would have to accompany the use of the ENUM.
  • you can allow for NULL (Example : game_rating ENUM('EC','E','E10+','T','M','A') NULL This allows a new game to be entered into a table without a default rating)

CAVEAT

OK game_rating may be a bad example since ESRB could create more game ratings. In that case, a FK would be better but the base table would have to have 'unknown' as a default type and define game_rating as NOT NULL.

Improve this answer
1
  • My recent experimenting seems to show that at least for MySQL, the issue of updating enum values is no longer an issue. The indexes update as you would expect (hope?)
    – jmadsen
    Commented Jul 9, 2016 at 10:39
1

When you'd be using a column with a check constraint (limiting possible values) in other DBMS, I'd model that as an ENUM in MySQL

Improve this answer

Not the answer you're looking for? Browse other questions tagged or ask your own question.