Unique constraint for table with column for soft delete

Question

I have a table with columns:

Id int
Name varchar *
Description varchar
LevelId int *
DeletedAt datetime nullable *

I want to have a unique constraint on the fields above marked with asterisks: Name, LevelId, DeletedAt. The reason I added DeletedAt for the constraint it so that when someone soft deletes and added a new record with the same Name and LevelId, the DB will allow the addition. But I was wrong thinking that 2 rows with the same Name, LevelId, and both NULL for DeletedAt would not be permitted as NULL is not equal to NULL.

What I need is an alternative for this. How can I support this requirement? One thing I can think of is replace DeletedAt with varchar and then it have a default value like for example "Active" or an empty string (just not null) and then put the date as a string for deleted rows. But I was thinking if there was a more elegant solution.

Answer 1

I like to use a unique filtered index for this. For your specific case, it'd look something like:

create unique filtered index FUIX_Name_LevelId
   on dbo.yourTable (Name, LevelID)
   where DeletedAt is null;

This will allow only one "active" row per (Name, LevelId) tuple. It also allows for as many "deleted" records as you'd like (since those rows won't qualify for the filter on the index, they aren't considered when determining uniqueness).

One thing that I've been bitten on in the past is: tables that have filtered indexes on them require certain query settings to be just so or queries against the table will fail. See the documentation on filtered indexes for more information.