The problem that I’m confused about is that this still allows the User object to be created more than once. I can create multiple Biller associations with the same User. Am I doing something stupid? Sorry, but I’m confused.
This test app is in Cuba Platform version is 7.2.15. The Biller entity has a field also called biller which is a unique and mandatory association to the sec$User entity with a 1-to-1 cardinality. When I go to the Biller editor, it allows me to create multiple records with a biller that has the same user.
You’ll want to create a User, then add the User in the Biller screen. After that, you should see that you can add the same User as another Biller, which shouldn’t be allowed by the unique constraint selected. It just seems that selecting ‘Unique’ as a constraint doesn’t create the constraint in the database.
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. 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.