220.127.116.11.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)