hsqldb unique constraint at database level not working

Support
#1

I am running into the following issue with soft deletion. I had a unique constraint for the name of an entity and the user id that created it. This prevented duplicates well. However, if I deleted an entity and then tried to recreate the same name, it would fail because the soft deleted version was still around.

The solution specified in the manual “4.2.1.4.3 Unique Constraints at Database Level” doesn’t seem to work, at least for hsqldb. I added DELETE_TS to the unique index and it now lets me create duplicates with the same name.

Attached is a project example where this happens. The user id gets set in @PostConstruct.

uniqueConstraint.zip (93.3K)

#2

Unfortunately, HSQL has the same behavior for nulls in unique indexes as mentioned in the docs for MySQL, which prevents from using unique constraints on soft-deleted entities.

We see it as a minor issue because HSQL should be used for prototyping only. Besides, we just don’t know how to fix it or make a suitably simple workaround.

#3

How is the cuba-platform development cycle supposed to work? Say I start prototyping with HSQL and then deploy to a MySQL DB in production, do I then have to change over to MySQL in development as well to ensure consistency?

Also, will the MySQL solution in the docs with the trigger also work for HSQL?

#4

Sure, at some point you should decide that your prototype is becoming a product and switch to the database that you are going to use in production. It should be easy - just change the database for the project and generate DB scripts in Studio.

will the MySQL solution in the docs with the trigger also work for HSQL?

Most likely, but we haven’t checked.