Inheritance strategy and unique constraints

MyEntityParent extends StandardEntity, inheritance strategy - joined. Unique constraints - OK.
MyEntityChild extends MyEntityParent - oops, “Cannot create unique constraints because the entity is soft deleted but there is no ‘delete_ts’ column in the entity’s table”.
Indeed, the “delete_ts” field in the child table is missing.
I need constrait.
How to overcome this problem in the most correct and most standard way?

Unfortunately there is no standard way for this case. Perhaps the simplest solution is to add a boolean field to the child entity and set it to true in BeforeDeleteEntityListener. Then you can create a partial unique index or an index including this field depending on your database type. DDL for this index should be added to 30.create-db.sql.

Hi, Konstantin can you better explain with a code example. I think you mean that mechanism of soft deletion including field ts_deleted and user who softly deleted record is not automatically generated by framework for inheritace stategy joined (but probably also for alternative ORM policies like single talble.( I tried to change strategy without any success). This should be manually coded using a listener which completes the requirede fields (or a trigger like in mysql if a database-level solution is requested). I migrated to postgres because it supports partial indexes and will be production environment as explained in (Soft deletion and unique constraint.). I think this is a recurring problem, given that in inheritance example is present.I think Cuba is a great piece of software;-) Thanks in advance.

Hi,

Let me explain the solution on the entities from the sample-entity-inheritance project. Suppose that the Person entity has the passport attribute and it must be unique. The entity is soft deleted, but it’s table has no DELETE_TS column because of JOINED inheritance, and Studio cannot create a unique index automatically.

First, switch your database to PostgreSQL in Project properties > Database type.

Then add two attributes to Person:

@Table(name = "DEMO_PERSON")
@Entity(name = "demo$Person")
public class Person extends Customer {
    // ...

    @Column(name = "PASSPORT", nullable = false, length = 100)
    protected String passport;

    @Column(name = "DELETED")
    protected Boolean deleted;

    // ...

In modules/core/db/init/postgres/30.create-db.sql define an index:

create unique index IDX_DEMO_PERSON_UN_PASSPORT on DEMO_PERSON (PASSPORT) where DELETED is null^

When you start the application from Studio, it will generate an update script for removing this index. Click Exclude on it, and it will not be generated anymore.

Create an entity listener for Person:

package com.company.entityinheritance.listener;

import org.springframework.stereotype.Component;
import com.haulmont.cuba.core.listener.BeforeDeleteEntityListener;
import com.haulmont.cuba.core.EntityManager;
import com.company.entityinheritance.entity.Person;

@Component("demo_PersonEntityListener")
public class PersonEntityListener implements BeforeDeleteEntityListener<Person> {

    @Override
    public void onBeforeDelete(Person entity, EntityManager entityManager) {
        entity.setDeleted(true);
    }
}

That’s it. The listener will fill in the deleted attribute when the entity is deleted, and the index will prevent from creating two records with the same passport, but only if the records are not deleted.

Thank you very much Konstantin, I’ ll try it and let you know.
I missed step where script to delete index is disabled.

I’ll wish you happy new year.

Fabrizio