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.
