It would be convenient for people who use UUIDs as primary key on a table to be able to explicitly set the UUID primary key column to use UUID() as the default value, similar to how IDENTITY works for int keys. The idea is to just insert values without a key and let hsql generate a UUID key on the fly. H2 syntax: id UUID DEFAULT RANDOM_UUID() PRIMARY KEY
This can be done with an insert trigger, but that's much less elegant and carries some overhead work that's easy to miss or break.
By default, HSQLDB follows the SQL Standard in this area and does not allow a function as the default value.
But in some syntax compatibility modes it does accept functions. Try:
set database sql syntax ora true
create table ttuuid (id uuid default uuid() primary key, val int)
I guess using an existing vendor dialect is indeed the most sensible solution if the sql spec forbids it.
Is there a particular reason h2 dialect isn't provided or has it just not been worked on yet? Seems like an important enough db along with derby and prostgres and such to be worth supporting.
It is probably as important as Derby but Derby dialect is simply a small subset of the DB2 dialect. There are no plans to support database dialects beyond those already supported.
Hm, ok. Thanks for all the info.
In the future, this supported Oracle compatibility feature can be made available by default (without using the ORA compatibility mode).