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?

Bijugate

Answer 1

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.

Celindaceline

By the way, it's possible that an enum and a reference table is more efficient. – Celindaceline

The catch is that there is no way to enumerate the possible values with this solution (though he may not need it). – Jaunita

You mean you don't know the possible values? How could you use enum then? Is your example correct? – Celindaceline

I mean you can't get a list of the accepted values in any way, except by looking at the constraint. – Jaunita

Answer 2

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.)

Stooge

It does have support now, as of version 1.4.195, released on 2017-04-23, but did not have when the question was written. Maybe you want to clarify in your answer. – Mullein

Answer 3

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

Jaunita

Minus 1 for "enum is not a great solution in any case" without reasoning or reference. – Tripper

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. – Nelda

Answer 4

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)

Supination

Answer 5

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.

Bran

Answer 6

This is now supported on H2, you can create a table with an enum column and even alter the enum later if needed.

CREATE TABLE example (
    "example" TEXT,
    "state" ENUM (
         'CREATED',
         'DELETED'
     )
);

ALTER TABLE example ALTER COLUMN "state" ENUM (
    'CREATED',
    'USED',
    'DELETED'
);

Kaminsky