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?

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.