How to allow a Boolean index only be unique for True

Question

I have a table that I'm doing logical deletes on. I have a column Name and a column Is_Active. Name is a varchar and Is_active is a bool.

I can't make Name unique because there will be multiple rows that have the same name. Where Is_Active == False.

I need to make sure that at any time there is only one record for a Name where Is_Active == True.

Is there a way I can do this ? Or can anybody suggest a better way ?

Answer 1

You can create a unique filtered index at database level:

CREATE UNIQUE INDEX UQ_EmployeeName
ON Employee (Name)
WHERE Is_Active = 1

The above index will not allow duplicate records like ('Bob', 1). It will allow though records like ('Bob', 1), ('Bob', 0) to co-exist in your database.

Answer 2

Allow a Filtered Index having condition of "not being 0". 0 representing false. This way, only one true value can exist. You won't have to create nullable column, just ignore the false value using filter.

Example of EF Core:

entity.HasIndex(x => new {x.ArticleId, x.IsActive}).IsUnique().HasFilter("[IsActive] != 0");

It's not a workaround so I love it!