Is there a way to create an UNIQUE INDEX in H2?

[Rodrigo]

I'm trying to add this statement in my h2 script:

CREATE UNIQUE INDEX "SOME_NAME" ON "SOME_TABLE_NAME" (COLUMN1,COLUMN2)

Looks like it is being bypassing, because I was able to insert two identical rows in my h2, but not in my actual DB (Oracle).

Is there a way to create that UNIQUE INDEX?

[Steve McLeod]

H2 does restrict inserts according to UNIQUE index constraints.

Can you post complete reproduction steps?

[Rodrigo]

Sure, this is my h2 Script:

CREATE TABLE "SOMETHING_TABLE"

  (

    "COLUMN1"           CHAR(10 BYTE),

    "COLUMN2"       CHAR(10 BYTE),

    "COLUMN3"          CHAR(2 BYTE),

    "COLUMN4"    VARCHAR2(9 BYTE)

  )

ALTER TABLE CMASTER ALTER COLUMN COLUMN1 SET NOT NULL;

  

CREATE UNIQUE INDEX "PK_SOMETHING_TABLE" ON "SOMETHING_TABLE"

  (

    "COLUMN1", "COLUMN2", "COLUMN3"

  )

[Rodrigo]

Actually there's a way, but it doesn't support composite index:

CREATE INDEX IDXNAME ON TEST(NAME)

So I would like to do something like:

CREATE INDEX IDXNAME ON TEST(SOME_COLUMN1,SOME_COLUMN2,SOME_COLUMN3)

[Noel Grandin]

For starters, your script does not run on H2 because it is using weird syntax.

However, if I fix it, H2 correctly throws a constraint violated exception.

​

CREATE TABLE SOMETHING_TABLE

  (

    COLUMN1           CHAR(10),

    COLUMN2       CHAR(10),

    COLUMN3          CHAR(2),

    COLUMN4    VARCHAR2(9)

  );

CREATE UNIQUE INDEX PK_SOMETHING_TABLE ON SOMETHING_TABLE

(

    COLUMN1, COLUMN2, COLUMN3

 );

insert into SOMETHING_TABLE values(1, 1, 1, 1) ;

insert into SOMETHING_TABLE values(1, 1, 1, 1) ;

[Rodrigo]

Yes, Sorry, I copied a bad formatted script. Regardless of that I just used your fixed script and it didn't work. I inserted 2 identical rows and I didn't get any error. All my Junit test passed.

[Rodrigo]

In fact the only way the unique index worked, was using a single field to index, like:

CREATE UNIQUE INDEX PK_SOMETHING_TABLE ON SOMETHING_TABLE( COLUMN1);

Try to create an index using more than one field, didn't work for me.

[Rodrigo]

I changed my mind. It worked, But for the scenario that I'm trying to test, it didn't work :)

It works fine if we use not null values related to the index, when inserting, for example:

insert into SOMETHING_TABLE values(1, 1, 1, 1) ;

insert into SOMETHING_TABLE values(1, 1, 1, 1) ;

Using above sentence, I will get an error.

But it I try to insert:

insert into SOMETHING_TABLE values(1, NULL, NULL, NULL) ;

insert into SOMETHING_TABLE values(1, NULL, NULL, NULL) ;

I won't get any errors and my Junit test passed without problems, which isn't what I was expecting

[Noel Grandin]

Confirmed that that this is indeed a bug, even in latest master, please log an issue in our tracker so we don't forget it.

https://github.com/h2database/h2database/issues

Thanks,

​

[Alexey Panchenko]

That's actually a feature defined by the SQL standard.

E.g. you can see "Null values are not considered equal" in https://www.postgresql.org/docs/current/static/indexes-unique.html

A workaround can be partial indexes if supported by the database, something like:

CREATE UNIQUE INDEX IDX_12_3NULL ON SOMETHING_TABLE (COLUMN1, COLUMN2) WHERE COLUMN3 IS NULL;

In H2 I think it should be possible to add computed columns with NOT NULL results and then index those.

Regards,

Alex

--
You received this message because you are subscribed to the Google Groups "H2 Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email to h2-database+unsubscribe@googlegroups.com.
To post to this group, send email to h2-da...@googlegroups.com.
Visit this group at https://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.