H2 database: unique constraint only over non-NULL values? (filtered index)

Question

SQL Server 2008 apparently has filtered indices.

What is the cleanest way to achieve the same in H2?

Answer 1

I believe you can use a computed column for this purpose . . . assuming you have a unique id. Let me assume the unique id is numeric and never negative. Then:

alter table t add col (case when <condition> then -1 else uniqueid end);

create unique index unq_t_col on t(col);

I believe that H2 supports unique indexes, computed columns, and indexes on computed columns, so this should work.