Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to handle enumerations without enum fields in a database?

Tags:

How would I implement a enumeration field in a database that doesn't support enumerations? (i.e. SQLite)

The fields need to be easily searchable with "field = ?" so using any type of data serialization is a bad idea.

like image 656
epochwolf Avatar asked Apr 17 '09 16:04

epochwolf


People also ask

Should enums be stored in database?

By keeping the enum in your database, and adding a foreign key on the table that contains an enum value you ensure that no code ever enters incorrect values for that column. This helps your data integrity and is the most obvious reason IMO you should have tables for enums.

What happens when no value is inserted in an enum list?

Empty or NULL Enumeration Values If you insert an invalid value into an ENUM (that is, a string not present in the list of permitted values), the empty string is inserted instead as a special error value.

How is enum saved in DB?

First of all, in order to save enum values in a relational database using JPA, you don't have to do anything. By default, when an enum is a part of an entity, JPA maps its values into numbers using the ordinal() method. What it means is that without customizations JPA stores enum value as numbers.

Can an enum have empty value?

MySQL accepts empty values for enumeration only if SQL mode is not set as TRADITIONAL, STRICT_TRANS_TABLES or STRICT_ALL_TABLES. Otherwise, MySQL would not accept empty values and throws an error. As we know that each enumeration value is having an index value, the empty value would have 0 index value.


2 Answers

Using a foreign key to a lookup table is the approach I use. In fact, I use this even when I do use a database that supports ENUM (e.g. MySQL).

For simplicity, I may skip the ever-present "id" for the lookup table, and just use the actual value I need in my main table as the primary key of the lookup table. That way you don't need to do a join to get the value.

CREATE TABLE BugStatus (   status            VARCHAR(20) PRIMARY KEY );  INSERT INTO BugStatus (status) VALUES ('NEW'), ('OPEN'), ('FIXED');  CREATE TABLE Bugs (   bug_id            SERIAL PRIMARY KEY,   summary           VARCHAR(80),   ...   status            VARCHAR(20) NOT NULL DEFAULT 'NEW',   FOREIGN KEY (status) REFERENCES BugStatus(status) ); 

Admittedly, storing strings takes more space than MySQL's implementation of ENUM, but unless the table in question has millions of rows, it hardly matters.

Other advantages of the lookup table are that you can add or remove a value from the list with a simple INSERT or DELETE, whereas with ENUM you have to use ALTER TABLE to redefine the list.

Also try querying the current list of permitted values in an ENUM, for instance to populate a pick-list in your user interface. It's a major annoyance! With a lookup table, it's easy: SELECT status from BugStatus.

Also you can add other attribute columns to the lookup table if you need to (e.g. to mark choices available only to administrators). In an ENUM, you can't annotate the entries; they're just simple values.

Another option besides a lookup table would be to use CHECK constraints (provided the database supports them -- MySQL doesn't support CHECK until version 8.0.16):

CREATE TABLE Bugs (   bug_id            SERIAL PRIMARY KEY,   summary           VARCHAR(80),   ...   status            VARCHAR(20) NOT NULL     CHECK (status IN ('NEW', 'OPEN', 'FIXED')) ); 

But this use of a CHECK constraint suffers from the same disadvantages as the ENUM: hard to change the list of values without ALTER TABLE, hard to query the list of permitted values, hard to annotate values.

PS: the equality comparison operator in SQL is a single =. The double == has no meaning in SQL.

like image 102
Bill Karwin Avatar answered Sep 22 '22 22:09

Bill Karwin


To restrict the possible values I would use a foreign key to a table that holds the enumeration items.

If you don't want to JOIN to do your searches then make the key a varchar if JOINS are not a problem then make the key an INT and don't join unless you need to search on that field.

Note that putting your enumerations in the DB precludes compile time checking of the values in your code (unless you duplicate the enumeration in code.) I have found this to be a large down side.

like image 40
Chris Nava Avatar answered Sep 26 '22 22:09

Chris Nava