Oct 5, 2022 by Robert Gravelle

In the realm of Information Technology, or IT as it's more commonly known, an enum is a special data type that encapsulates a set of predefined constants. As such, the variable may only hold one of the values that have been predefined for it. Common examples include compass directions of NORTH, SOUTH, EAST, and WEST or the days of the week.

One of the complicating factors when storing enums in a database table is that their values may be numeric or alphabetic (i.e. strings). Moreover, you'll want to prevent users from adding any values to the table that are not part of the permissible enum set. We'll be addressing both of these issues in today's blog.

Enum Values Explored

The most basic enums contain a set of zero-based ordinal values each represented by a constant, seen below in Java:

public enum Day {
    SUNDAY, MONDAY, TUESDAY, WEDNESDAY,
    THURSDAY, FRIDAY, SATURDAY 
}

More complex enums may also contains other types; strings are the most common, but more complex objects are also supported. Here is an enum for representing different environment URLs (also in Java):

public enum Environment 
{
    PROD("https://prod.domain.com:1088/"), 
    SIT("https://sit.domain.com:2019/"), 
    CIT("https://cit.domain.com:8080/"), 
    DEV("https://dev.domain.com:21323/");
 
    private String url;
 
    Environment(String envUrl) {
        this.url = envUrl;
    }
 
    public String getUrl() {
        return url;
    }
}

Generally, it is considered bad practice to store enumerations as numerical ordinal values, as it makes debugging and support difficult. It's usually preferable to store the actual enumeration value converted to string. To illustrate, imagine that we had an enum of card suits:

public enum Suit { 
  Spade, 
  Heart, 
  Diamond, 
  Club 
}

Now imagine that you are a database practitioner trying to decipher either of the following query results:

Name          Suit
------------  ----
John Smith    2
Ian Boyd      1

Name          Suit
------------  -------
John Smith    Diamond
Ian Boyd      Heart

I think that you will agree that the latter is much easier to interpret as the first option requires getting at the source code and finding the numerical values that were assigned to each enumeration member.

Although storing strings takes more disk space, enumeration member names tend to be short, and hard drives are cheap, making the trade-off worth while to make your day-to-day job easier.

Another problem with using numerical values is that they are difficult to update; you cannot easily insert or rearrange the members without having to force the old numerical values. For example, adding a value of Unknown to the Suit enumeration would require you to update it to:

public enum Suit { Unknown = 4, Heart = 1, Club = 3, Diamond = 2, Spade = 0 }

...in order to maintain the legacy numerical values already stored in the database.

Validating Enum Values In the Database

Many modern databases, including MySQL and SQL Server, support the ENUM data type. Specified as strings, ENUM values are automatically encoded as numbers when stored for compact storage.

Here are the MySQL statements in Navicat for MySQL to create and populate a table with shirts and their sizes, as well as a SELECT query that fetches medium sized shirts:

If we now try to insert an invalid ENUM value, we get the following error:

Although the message states the the value was truncated, the data was not actually inserted.

Conclusion

In today's blog, we explored how to with enum values in the database, including how to store, validate, insert, and retrieve them.

Interested in trying Navicat for MySQL? You can use it for 14 days for free!