3.3.4. MySQL Database Specifics

For the databases with the charset different from UTF-8, the framework scripts with constraints cannot be executed. In this case, modify the Charset and Collation name database properties by passing the following parameters in the Connection params field of the project properties:

?useUnicode=true&characterEncoding=UTF-8

MySQL does not support partial indexes, so the only way to implement a unique constraint for a soft deleted entity is to use the DELETE_TS column in the index. But there is another problem: MySQL allows multiple NULLs in a column with a unique constraint. Since the standard DELETE_TS column is nullable, it cannot be used in the unique index. We recommend the following workaround for creating unique constraints for soft deleted entities:

  1. Create a DELETE_TS_NN column in the database table. This column is not null and is initialized by a default value:

    create table DEMO_CUSTOMER (
    ...
    DELETE_TS_NN datetime(3) not null default '1000-01-01 00:00:00.000',
    ...
)

  2. Create a trigger that will change DELETE_TS_NN value when DELETE_TS value is changed:

    create trigger DEMO_CUSTOMER_DELETE_TS_NN_TRIGGER before update on DEMO_CUSTOMER
for each row
    if not(NEW.DELETE_TS <=> OLD.DELETE_TS) then
        set NEW.DELETE_TS_NN = if (NEW.DELETE_TS is null, '1000-01-01 00:00:00.000', NEW.DELETE_TS);
    end if

  3. Create a unique index including unique columns and DELETE_TS_NN:

    create unique index IDX_DEMO_CUSTOMER_UNIQ_NAME on DEMO_CUSTOMER (NAME, DELETE_TS_NN)