Hi, Yes, H2 creates unique indexes for constraints. If you have a complete, standalone, simple reproducible test case, we can have a look. But please remove all columns that are not needed to analyze this.
Regards, Thomas On Fri, Mar 3, 2017 at 8:58 PM, Laird Nelson <[email protected]> wrote: > I have a table with multiple columns in its primary key. > > CREATE TABLE GAV ( > GROUP_ID VARCHAR(255) NOT NULL, > ARTIFACT_ID VARCHAR(255) NOT NULL, > VERSION_ID VARCHAR(20) NOT NULL, > CLASSIFIER VARCHAR(20) DEFAULT 'jar' NOT NULL > PRIMARY KEY (GROUP_ID, ARTIFACT_ID, VERSION_ID, CLASSIFIER) > ) > > > I'm seeing some very odd behavior where an attempt to MERGE INTO this > table is bumping into a unique constraint violation—as though a unique > constraint were defined on each individual column, rather than on the group > of columns! > > If I go into the mydb.mv.db file, my actual table definition stored in > that file, minus some control characters and other binary stuff, is this: > > CREATE CACHED TABLE PUBLIC.GAV( > GROUP_ID VARCHAR(255) NOT NULL, > ARTIFACT_ID VARCHAR(255) NOT NULL, > VERSION_ID VARCHAR(20) NOT NULL, > CLASSIFIER VARCHAR(20) DEFAULT 'jar' NOT NULL > ) > > > Looks good so far. Then I see: > > CREATE PRIMARY KEY PUBLIC.PRIMARY_KEY_1 ON PUBLIC.GAV(GROUP_ID, > ARTIFACT_ID, VERSION_ID, CLASSIFIER) > > > Also looks good. And then: > > ALTER TABLE PUBLIC.GAV ADD CONSTRAINT PUBLIC.CONSTRAINT_1 PRIMARY > KEY(GROUP_ID, ARTIFACT_ID, VERSION_ID, CLASSIFIER) INDEX > PUBLIC.PRIMARY_KEY_1 > > > Great. Everything looks fine. > > Then, like I said, a part of my code does: > > MERGE INTO GAV(GROUP_ID, ARTIFACT_ID, VERSION_ID, CLASSIFIER) VALUES(?, ?, > ?, ?) > > > ...and I get: > > Unique index or primary key violation: "CONSTRAINT_INDEX_112 *ON > PUBLIC.GAV(VERSION_ID)* VALUES ('3.0.0.CR1', 1)"; SQL statement: > MERGE INTO GAV(GROUP_ID, ARTIFACT_ID, VERSION_ID, CLASSIFIER) > VALUES(?, ?, ?, ?) [23505-193] > > > Huh? I checked the four values; they're distinct from any other four > values in the table. This shouldn't be a primary key violation. > > I couldn't find CONSTRAINT_INDEX_112 in the INFORMATION_SCHEMA.SYSTEM_ > TABLE area, so I looked back at the database file and found this oddness: > > CREATE UNIQUE INDEX PUBLIC.CONSTRAINT_INDEX_112 *ON > PUBLIC.GAV(VERSION_ID)* > > > What? I didn't create this index. I'm guessing it's an index that is > automatically created for a foreign key that references the VERSION_ID > column? > > Indeed, I have another table that does this: > > CREATE TABLE IF NOT EXISTS QUALIFICATION ( > GROUP_ID VARCHAR(255) NOT NULL REFERENCES GAV(GROUP_ID), > ARTIFACT_ID VARCHAR(255) NOT NULL REFERENCES GAV(ARTIFACT_ID), > * VERSION_ID VARCHAR(20) NOT NULL REFERENCES GAV(VERSION_ID),* > CLASSIFIER VARCHAR(20) NOT NULL DEFAULT 'jar' REFERENCES GAV(CLASSIFIER), > JAVA_TYPE_ID INT NOT NULL REFERENCES JAVA_TYPE(ID), > QUALIFIER_ID INT NOT NULL REFERENCES QUALIFIER(JAVA_TYPE_ID), > USAGE_ID CHAR NOT NULL REFERENCES USAGE(ID), > PRIMARY KEY(GROUP_ID, ARTIFACT_ID, VERSION_ID, CLASSIFIER, JAVA_TYPE_ID, > QUALIFIER_ID, USAGE_ID) > ); > > > This all seems like reasonable DDL to me. Is this a bug or am I doing > something wrong? > > I'm using H2 1.4.193 and Java 8. > > -- > 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 [email protected]. > To post to this group, send email to [email protected]. > 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 [email protected]. To post to this group, send email to [email protected]. Visit this group at https://groups.google.com/group/h2-database. For more options, visit https://groups.google.com/d/optout.
