How to replace enum type in H2 database?

Question

MySQL dialect:

CREATE TABLE My_Table ( my_column enum ('first', 'second', ... 'last'));

H2 dialect:

CREATE TABLE My_Table ( my_column ? ('first', 'second', ... 'last'));

What type is equivalent in H2 too the enum type from MySQL?

score 17 · Accepted Answer · answered Feb 20 '12 at 19:03

17

I'm not sure if this is what you are looking for, but would you could do is use a check constraint:

CREATE TABLE My_Table(my_column varchar(255) 
    check (my_column in ('first', 'second', 'last')));

-- fails:
insert into My_Table values('x');

-- ok:
insert into My_Table values('first');

This will work in H2, Apache Derby, PostgreSQL, HSQLDB, and even SQLite. I didn't test other databases.

answered Feb 20 '12 at 19:03

Thomas Mueller

48,905
14
116
132

By the way, it's possible that an enum and a reference table is more efficient. – Thomas Mueller Feb 20 '12 at 19:04
The catch is that there is no way to enumerate the possible values with this solution (though he may not need it). – Viruzzo Feb 21 '12 at 07:29
You mean you don't know the possible values? How could you use enum then? Is your example correct? – Thomas Mueller Feb 21 '12 at 08:00
I mean you can't get a list of the accepted values in any way, except by looking at the constraint. – Viruzzo Feb 21 '12 at 12:57

score 5 · Answer 2 · answered Oct 09 '17 at 03:32

5

Looks like H2 has enums: http://www.h2database.com/html/datatypes.html#enum_type

So the exact same syntax should work. (I don't know how closely the semantics match.)

answered Oct 09 '17 at 03:32

nafg

2,424
27
25

3

It does have support now, [as of **version 1.4.195**, released on 2017-04-23](http://www.h2database.com/html/changelog.html), but did not have when the question was written. Maybe you want to clarify in your answer. – Cedric Reichenbach Mar 01 '18 at 16:37

score 5 · Answer 3 · answered Feb 20 '12 at 12:40

5

There is none; still, enum is not a great solution in any case, just use a a reference table.

answered Feb 20 '12 at 12:40

Viruzzo

3,025
13
13

7

Minus 1 for "enum is not a great solution in any case" without reasoning or reference. – Raphael Oct 14 '19 at 12:59
The `enum` type can be required for many reasons. A couple of examples: 1) matching a test H2 database structure to an existing, production, database that cannot be altered and requires this type; 2) removing the burden of joining tables and handling inserts/updates in reference tables when the `enum` field is strongly typed, whose options are not prompt to change. – Uyric Jun 30 '21 at 16:53

score 3 · Answer 4 · answered Jan 03 '18 at 09:07

3

Upgrade h2 jar

H2 maven repository: https://mvnrepository.com/artifact/com.h2database/h2

jar versions:

1.4.196 Central (Jun, 2017) - enum support (tested)

1.4.192 Central (May, 2016) - lack of enum support (also tested)

answered Jan 03 '18 at 09:07

blueberry0xff

3,707
30
18

thesquaregroot · Answer 5 · 2019-01-15T04:01:25.943

I ran into this problem and solved it by creating a separate table and foreign key constraint.

CREATE TABLE My_Enum_Table (
    my_column varchar(255) UNIQUE
);

INSERT INTO My_Enum_Table (my_column)
VALUES
    ('First'),
    ('Second'),
    ...
    ('Last');

CREATE TABLE My_Table (
   my_column varchar(255),
   FOREIGN KEY (my_column) REFERENCES My_Enum_Table (my_column)
);

That way when you try to do an INSERT into My_Table it will do a foreign key check to make sure the value you're inserting is in My_Enum_Table.

There are some trade-offs here though:

PROS
- You can still interact with this the same way you would an ENUM.
- You also get a little extra flexibility in the sense that you can add another value without having to alter table definitions.
CONS
- This is likely slower than an ENUM since it has to do a table look-up. Realistically though since the table should have a reasonably small number of rows this is probably fairly negligible. Adding an index to My_Table.my_column may help with this.
- This prevents the need to join with the reference table but with basically the same level of complexity from a database perspective. Though this probably isn't a big deal unless you're concerned about cluttering your database with another table.
- This also requires you to use an engine that support foreign keys, such as INNODB. I'm not sure if this is really a CON but I suppose it could be for someone with specialized needs.

How to replace enum type in H2 database?

5 Answers5