Does it make sense to put a unique constraint on a nullable soft-delete flag?

Question

So we currently have the following:

MYTABLE
COLUMN: ID (INTEGER Primary key, auto-incrementer)
COLUMN: START (DATE)
COLUMN: COMPANYID (INTEGER, Foreign key to COMPANY)
COLUMN: DELETED (INTEGER)
CHECK: DELETED = 0 OR DELETED = 1

Now, there's the requirement to allow infinite deleted records, but to only allow a single non-deleted record for each date+company.

I suggested to change the schema to:

MYTABLE
COLUMN: ID (INTEGER Primary key, auto-incrementer)
COLUMN: START (DATE)
COLUMN: COMPANYID (INTEGER, Foreign key to COMPANY)
COLUMN: ACTIVE (Nullable INTEGER)
CHECK: ACTIVE = 1 OR ACTIVE IS NULL
UNIQUE: START, COMPANYID, ACTIVE

While my coworker is of the opinion that that is "going too far with constraints" and that we should just rely on the uniqueness checking in the application.

Is there a generally accepted best practice here?

Answer 1

You don't mention what DBMS you are referring to, but your suggested unique constraint won't work for several out there, because null is not equal to null

create table t (x int not null, y int, unique(x,y));
insert into t (x) values (1);
insert into t (x) values (1);

Postgres12
Does not violate the constraint

Oracle18c
Violates constraint

Db2 Developer C 11.1
Does not accept nullable columns in unique constraint

Firebird 3.0
Violates constraint

MariaDB 10.4
Does not violate the constraint

MySQL 8.0
Does not violate the constraint

SQLlite 3.27
Does not violate the constraint

SQLServer 2017
Violates constraint

Tested at DB<>Fiddle

So, generally speaking, your UNIQUE constraint does not work. Consider changing ACTIVE to

ACTIVE SMALLINT NOT NULL,
CHECK (ACTIVE BETWEEN 0 AND 1),
UNIQUE (START, COMPANYID, ACTIVE)

Though I'm not convinced that a certain COMPANYID at a particular START can be both ACTIVE and NOT ACTIVE, but then I don't know your business.

EDIT: Given the new info in the question, something like this might be possible (I don't have access to the docs for system i right now, so I don't know whether generated columns, or constraints on expressions exists)

create table t 
( a int not null generated always as identity
, x int not null
, y int
, z int not null generated always as ( 
    case when y is null then -1*a else y end 
)
, unique(x,z)
, check (y is null or y = 1)
);

insert into t(x) values (1); -- ok
insert into t(x) values (1); -- ok
insert into t(x,y) values (1,1); -- ok
insert into t(x,y) values (1,1); -- fails because of constraint violation.