Re: [h2] Re: Is there a way to create an UNIQUE INDEX in H2?
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 On Sun, Oct 9, 2016 at 12:31 PM, Noel Grandin wrote: > 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, > > > -- > 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+unsubscr...@googlegroups.com. > To post to this group, send email to h2-database@googlegroups.com. > Visit this group at https://groups.google.com/group/h2-database. > For more options, visit https://groups.google.com/d/optout. > -- 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+unsubscr...@googlegroups.com. To post to this group, send email to h2-database@googlegroups.com. Visit this group at https://groups.google.com/group/h2-database. For more options, visit https://groups.google.com/d/optout.
Re: [h2] Re: Is there a way to create an UNIQUE INDEX in H2?
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, -- 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+unsubscr...@googlegroups.com. To post to this group, send email to h2-database@googlegroups.com. Visit this group at https://groups.google.com/group/h2-database. For more options, visit https://groups.google.com/d/optout.
Re: [h2] Re: Is there a way to create an UNIQUE INDEX in H2?
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 -- 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+unsubscr...@googlegroups.com. To post to this group, send email to h2-database@googlegroups.com. Visit this group at https://groups.google.com/group/h2-database. For more options, visit https://groups.google.com/d/optout.
Re: [h2] Re: Is there a way to create an UNIQUE INDEX in H2?
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. El sábado, 8 de octubre de 2016, 22:49:29 (UTC-3), Rodrigo escribió: > > 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. > -- 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+unsubscr...@googlegroups.com. To post to this group, send email to h2-database@googlegroups.com. Visit this group at https://groups.google.com/group/h2-database. For more options, visit https://groups.google.com/d/optout.
Re: [h2] Re: Is there a way to create an UNIQUE INDEX in H2?
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. -- 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+unsubscr...@googlegroups.com. To post to this group, send email to h2-database@googlegroups.com. Visit this group at https://groups.google.com/group/h2-database. For more options, visit https://groups.google.com/d/optout.
Re: [h2] Re: Is there a way to create an UNIQUE INDEX in H2?
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), COLUMN4VARCHAR2(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) ; -- 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+unsubscr...@googlegroups.com. To post to this group, send email to h2-database@googlegroups.com. Visit this group at https://groups.google.com/group/h2-database. For more options, visit https://groups.google.com/d/optout.
[h2] Re: Is there a way to create an UNIQUE INDEX in H2?
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) El viernes, 7 de octubre de 2016, 10:29:02 (UTC-3), Rodrigo escribió: > > 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" > ) > > > El viernes, 7 de octubre de 2016, 5:19:08 (UTC-3), Steve McLeod escribió: >> >> H2 does restrict inserts according to UNIQUE index constraints. >> >> Can you post complete reproduction steps? >> >> >> >> On Wednesday, 5 October 2016 18:44:38 UTC+2, Rodrigo wrote: >>> >>> 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? >>> >>> >>> >>> -- 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+unsubscr...@googlegroups.com. To post to this group, send email to h2-database@googlegroups.com. Visit this group at https://groups.google.com/group/h2-database. For more options, visit https://groups.google.com/d/optout.
[h2] Re: Is there a way to create an UNIQUE INDEX in H2?
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" ) El viernes, 7 de octubre de 2016, 5:19:08 (UTC-3), Steve McLeod escribió: > > H2 does restrict inserts according to UNIQUE index constraints. > > Can you post complete reproduction steps? > > > > On Wednesday, 5 October 2016 18:44:38 UTC+2, Rodrigo wrote: >> >> 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? >> >> >> >> -- 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+unsubscr...@googlegroups.com. To post to this group, send email to h2-database@googlegroups.com. Visit this group at https://groups.google.com/group/h2-database. For more options, visit https://groups.google.com/d/optout.
[h2] Re: Is there a way to create an UNIQUE INDEX in H2?
H2 does restrict inserts according to UNIQUE index constraints. Can you post complete reproduction steps? On Wednesday, 5 October 2016 18:44:38 UTC+2, Rodrigo wrote: > > 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? > > > > -- 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+unsubscr...@googlegroups.com. To post to this group, send email to h2-database@googlegroups.com. Visit this group at https://groups.google.com/group/h2-database. For more options, visit https://groups.google.com/d/optout.
