5.2.1.4.3. Unique Constraints at Database Level

In order to apply unique constraints for certain value in the soft deletion mode, at least one non-deleted record with this value and an arbitrary number of deleted records with the same value may exist in database.

This logic can be implemented in a specific way for each database server type:

  • If database server supports partial indexes (e.g. PostgreSQL), unique restrictions can be achieved as follows:

    create unique index IDX_SEC_USER_UNIQ_LOGIN on SEC_USER (LOGIN_LC) where DELETE_TS is null

  • If database server does not support partial indexes (e.g. Microsoft SQL Server 2005), DELETE_TS field can be included in the unique index:

    create unique index IDX_SEC_USER_UNIQ_LOGIN on SEC_USER (LOGIN_LC, DELETE_TS)