Conditional Unique index on h2 database

Question

I have a SAMPLE_TABLE with a column BIZ_ID which should be unique when the column active is not equal to 0.

On an oracle database the index looks like this:

  CREATE UNIQUE INDEX ACTIVE_ONLY_IDX ON SAMPLE_TABLE (CASE "ACTIVE" WHEN 0 THEN NULL ELSE "BIZ_ID" END );

How would this unique index look like on a h2 database?

Histaminase

Even if Oracle can do this, don't. Jump back to your data modeling classes -- unique indexes should always be unique. you might try a function based index... – Fiend

Answer 1

In H2, you could use a computed column that has a unique index:

create table test(
    biz_id int, 
    active int,
    biz_id_active int as 
      (case active when 0 then null else biz_id end) 
      unique
 );
 --works
 insert into test(biz_id, active) values(1, 0);
 insert into test(biz_id, active) values(1, 0);
 insert into test(biz_id, active) values(2, 1);
 --fails
 insert into test(biz_id, active) values(2, 1);

Hollyanne

that didn't work for me as-is. Instead of (case active when 0 then null else biz_id end) I had to use (case when active=0 then null else biz_id end) – Lampe