How to handle enum without enum fields in a database?

Question

How do I implement an enumeration field in a database that does not support enumerations? (i.e. SQLite)

Fields should be easily searchable with " field =?" therefore, using any type of data serialization is a bad idea.

Answer 1

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

For simplicity, I can skip the ubiquitous " id " for the lookup table and simply use the actual value that I need in my main table as the primary key of the lookup table. This way you do not need to make a connection 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) ); 

It's clear that storing rows takes up more space than the MySQL ENUM implementation, but if the table doesn't have millions of rows, that 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 , while with ENUM you need to use ALTER TABLE to override the list.

Also try querying the current list of valid values ​​in ENUM , for example, to populate a collection in your user interface. This is a serious annoyance! Using the lookup table is easy: SELECT status from BugStatus .

You can also add other attribute tables to the lookup table if you need to (for example, mark options that are available only to administrators). In ENUM you cannot comment on posts; they are just simple meanings.

Another option, besides the lookup table, would be to use CHECK restrictions (assuming the database supports them - MySQL does not work):

 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 the CHECK constraint has the same drawbacks as ENUM : it is difficult to change the list of values ​​without ALTER TABLE , it is difficult to request a list of valid values, it is difficult to comment on the values.

PS: the equality comparison operator in SQL is single = . Double == doesn't make sense in SQL.

Answer 2

To limit the possible values, I would use a foreign key for a table containing enumeration elements.

If you do not want to JOIN your search, then make the varchar key, if JOINS is not a problem, then make the INT key and do not join if you do not need to search in this field.

Please note that adding your enumerations to the database does not allow you to check the compilation time of values ​​in your code (unless you duplicate the enumeration in the code.) I found that this is a big downside.

Answer 3

Basically you have two options:

• use the whole field

• use the varchar field

I would personally advocate using varchars because you won’t break anything if you change your enum +, the fields will be human readable, but ints have pro pro, namely performance (data size is an obvious example)

Answer 4

This is what I did recently.

In my hibernate, the POJO is mapped — I saved the member type as String, and it is VARCHAR in the database.

The tool for this accepts an enumeration. There is another setter that accepts String-, but it is private (or you can directly display the field - if that is what you prefer.)

Now the fact that I'm using String is encapsulated from everyone. For the rest of the application, my domain objects use an enumeration. And as for the database, I am using String.

If I missed your question, I apologize.

Answer 5

I would use varchar. Is this not an option for you?