CODEWITHSUNDEEP
hsqldbliquibase
Nicolas
Nicolas

Reputation: 69

HSQLDB unable to drop foreign key constraint object not found

Our HSQLDB database has a FK constraint from the PAYMENTS table to the USERS table. What we did wrong here was create a constraint without giving it a specific name. This causes HSQLDB to generate a name for you, e.g. SYS_FK_10985.

What I did was write a custom change set for Liquibase that will find the name of the index and drop it. What the script does is pretty simple:

SELECT constraint_name FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE WHERE TABLE_NAME= 'PAYMENTS' AND COLUMN_NAME= 'USER_ID';

If found, it is dropped by the following query executed in that same change set:

ALTER TABLE PAYMENTS DROP CONSTRAINT SYS_FK_10985;

The patch is executed successfully and the drop constraint command is added to the .log file of HSQLDB, however, when we then want to run the HSQLDB instance it throws an error saying the object could not be found.

HSQLDB throws exception

The log file looks as follows:

DB log file

The major problem is that the wrong index (a non-existent one) is dropped in the log file, which naturally causes HSQLDB to throw an exception. What is even worse: when the exception occurs, everything after that line is deleted from the log file and not even stored in the .data file.

Is it possible the constraint name changes causing the change set to get a wrong name?

Upvotes: 0

Views: 1062

Answers (1)

fredt
fredt

Reputation: 24372

You need to perform a CHECKPOINT right after this operation, or any series of structural changes.

The CHECKPOINT persists the changes into the .script file and deletes the .log file, avoiding the issue to arise.

Upvotes: 2

Related Questions