H2 - Create unique index with two columns

Question

currently I am using a postgres query to create two unique indexes.

Each index consists of two columns, where the value of one column is checked for null/not null:

CREATE UNIQUE INDEX deleted_not_null_idx 
ON user (ADDRESS, DELETED)
WHERE DELETED IS NOT NULL;

CREATE UNIQUE INDEX deleted_null_idx 
ON user (ADDRESS)
WHERE DELETED IS NULL;

I am attempting to do the same on H2 but I am having issues understanding the syntax and structure of H2.

How would this expression be formed if written using H2 syntax?

`... WHERE ...` -- What you want is a "partial index". As far as I know those are not implemented in H2. Only Oracle, DB2, PostgreSQL, and SQL Server implement partial indexes. — The Impaler, Mar 17 '20 at 15:01
So there is no possible way to create a partial index? I have seen things such as this: https://stackoverflow.com/questions/43559840/h2-database-unique-constraint-only-over-non-null-values-filtered-index and https://stackoverflow.com/questions/28836704/conditional-unique-index-on-h2-database but having issues understanding/using these answers/examples — Oozeerally, Mar 17 '20 at 15:03
What's do you want to achieve with the partial index? Uniqueness of the columns on the subset of rows and/or fast access with the index? Good question by the way +1. — The Impaler, Mar 17 '20 at 15:08
@TheImpaler "Uniqueness of the columns on the subset of rows" - thank you, it's had me stumped all day! — Oozeerally, Mar 17 '20 at 15:13

score 8 · Accepted Answer · answered Mar 17 '20 at 15:28

A workaround to ensure "uniqueness of the columns on a subset of rows" can be worked out if you are willing to add an artificial extra column to the table, just for this purpose. Not sure it's the best idea, but can do the job.

For example:

create table t (
  address varchar(20),
  deleted int,
  extra_column varchar(20) as
    case when deleted is null then null else address end,
  constraint uq1 unique (extra_column)
);

insert into t (address, deleted) values ('123 Maple', 20);
insert into t (address, deleted) values ('456 Oak', 25);
insert into t (address, deleted) values ('456 Oak', null); -- succeeds
insert into t (address, deleted) values ('456 Oak', 28); -- fails

Result:

select * from t;

ADDRESS    DELETED  EXTRA_COLUMN
---------  -------  ------------
123 Maple       20  123 Maple   
456 Oak         25  456 Oak     
456 Oak     <null>  <null>

Thanks for the solution, ideally i'd like to be able to implement this without the use of an extra column - but it is nevertheless a good solution. — Oozeerally, Mar 17 '20 at 15:34

H2 - Create unique index with two columns

1 Answers1