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.
The log file looks as follows:
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?
