Does HSQLDB support default values for enums in MySQL compat mode?

Question

I'm using an in-memory HSQLDB instance (version 2.5.1) for Java unit tests. The instance has MySQL compatibility mode enabled. The following create statement works:

CREATE TABLE people
(
  is_subscribed enum('y','n') NOT NULL
)

However, if I try adding in a default value like this:

CREATE TABLE people
(
  is_subscribed enum('y','n') DEFAULT 'n' NOT NULL
)

I get the following error message:

java.sql.SQLSyntaxErrorException: unexpected token: DEFAULT : line: 3 in statement [CREATE TABLE people

Is there a way to specify a default value for an enum type?

Answer 1

HSQLDB versions up to 2.5.1 do not support the DEFAULT clause directly for MySQL ENUM declarations. This may be supported in later versions.

You can add the DEFAULT after the table is created. Use:

ALTER TABLE people ALTER COLUMN is_subscribed SET DEFAULT 'n'

Update: This is now supported in the latest code. You can check out the code and build the jar. The update will appear in the next release.