updating self referencing row will cause an "Referential integrity constraint violation" on a migrated h2 database

[Ralf]

Hi,

we have a database which was migrated several times by different h2 and model versions up to the newest h2 and schema version. On a new created h2 database with the same schema the "Referential integrity constraint violation" will not come up.

shorted ddl:

CREATE TABLE "PUBLIC"."TERMEJB"
(
   ID integer PRIMARY KEY NOT NULL,
   MASTERTERM_ID integer,
  [.... many other columns]
);
FOREIGN KEY (MASTERTERM_ID) REFERENCES "PUBLIC"."TERMEJB"(ID);

Data:

INSERT INTO "PUBLIC"."TERMEJB" (ID,MASTERTERM_ID, [...other columns]) VALUES (2754, NULL, [...]);

Failing query:

UPDATE TERMEJB SET MASTERTERM_ID = 2754 WHERE (ID = 2754)

causes:

Error: Referential integrity constraint violation: "FK_TERMEJB_MASTERTERM_ID: PUBLIC.TERMEJB FOREIGN KEY(MASTERTERM_ID) REFERENCES PUBLIC.TERMEJB(ID) (2754)"; SQL statement:
UPDATE TERMEJB SET MASTERTERM_ID = 2754 WHERE (ID = 2754) [23503-173]
SQLState:  23503
ErrorCode: 23503


Is the attached database corrupt or is there a bug in h2?
Is there any possiblility to fix the database programmatically?

thx
    Ralf

[Noel Grandin]

Hi

There is some kind of constraint corruption here - the foreign key has become associated with the wrong index
internally. Not sure how that happened. It was probably a bug in an earlier version that we've fixed already, since I
could not find a sequence of commands that managed to replicate the situation.

This sequence seems to fix your problem :

ALTER TABLE TERMEJB DROP CONSTRAINT FK_TERMEJB_MASTERTERM_ID;
DROP INDEX TERMIDMASTERTERMFKINDEX;
ALTER TABLE TERMEJB ADD FOREIGN KEY (MASTERTERM_ID) REFERENCES TERMEJB(ID);

Just be sure to make a backup before you try it.

Regards, Noel.

On 2013-11-11 15:20, Ralf wrote:
> FOREIGN KEY (*MASTERTERM_ID*) REFERENCES "PUBLIC"."TERMEJB"(*ID*);

[Ralf]

thx