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.

In SQL Server, only one `NULL` is allowed in a unique constraint, exactly as you wanted. — Damien_The_Unbeliever, Dec 29 '17 at 08:33
See e.g. [this question](https://stackoverflow.com/q/767657/15498) which is just one of many where people are *seeking* the behaviour you (not unreasonably) *assumed* to be the case already. — Damien_The_Unbeliever, Dec 29 '17 at 08:38
I am using SQL Server 2014 and in the schema above, I can enter 2 records with same Name, LevelId, and both NULL on DeletedAt. What I need is for that to be not allowed. I initially thought it wouldn't be allowed because I thought NULL would equal NULL but it doesn't restrict me from entering same values for 2 records. I need something to replace the nullable DeletedAt column. — g_b, Dec 29 '17 at 10:06
I created a table in SQL Server 2014 based on your description above and tried to enter two rows with matching Name, Description and DeletedAt. The error message is `Msg 2601, Level 14, State 1, Line 1 Cannot insert duplicate key row in object 'dbo.T' with unique index 'IX_T'. The duplicate key value is (abc, def, ). ` — Damien_The_Unbeliever, Dec 29 '17 at 10:08
In fact, this is even stated *in the [documentation](https://learn.microsoft.com/en-us/sql/relational-databases/tables/unique-constraints-and-check-constraints#Unique)* - "... UNIQUE constraints allow for the value NULL. However, as with any value participating in a UNIQUE constraint, only one null value is allowed per column" — Damien_The_Unbeliever, Dec 29 '17 at 10:13
Hmmm, that's weird, I'll check my table, I have 3 rows right now with same Name, LevelId, and all NULL DeletedAt, and have this unique index: LevelId (ASC), Code (ASC), DeletedAt (ASC). — g_b, Dec 29 '17 at 10:14
Try to construct a complete script for us that reproduces your issue. It should ideally be a `CREATE TABLE`, `CREATE INDEX` and `INSERT` that runs without error. Like I said, when I constructed such a script based on your description, I got error 2601 rejecting the duplicate. — Damien_The_Unbeliever, Dec 29 '17 at 10:15
Ok, I will post a bit later, I can't right now, I appreciate the help. — g_b, Dec 29 '17 at 10:17
@Damien_The_Unbeliever: You are correct, I've tried manually creating a table and the behavior is just what you said. My table was created via EF Core Code First, it seems by default, it created a unique filtered index (WHERE DeletedAt IS NOT NULL). Thank you for the help. — g_b, Dec 30 '17 at 02:09

score 5 · Answer 1 · answered Dec 29 '17 at 10:04

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.

score 0 · Answer 2 · answered Aug 22 '19 at 13:00

Create unique compound key of 3 columns. First one is your unique column and second is LevelId,Last is DeletedAt(Any delete parameter can be delete time or numeric values - incremental value if deleted and 0 if not soft deleted)

CREATE UNIQUE NONCLUSTERED INDEX TABLE_NAME_DELETED_AT_NonClusteredIndex ON 
TABLE (
Name, LevelId, DeletedAt
) 
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

score 0 · Answer 3 · answered Jun 08 '20 at 02:16

I got the same scenario but needed to filter with two conditions. But filtered index does not support OR conditions, but AND operator is supported.

In case any one face same kind of scenario, they can use IN operator to achieve what the want.

Ex:

create unique index FUIX_Name_LevelId
on dbo.yourTable (Name, LevelID)
where DeletedAt = 1 OR DeletedAt = 0; -- this will give an error.

--solution
create unique index FUIX_Name_LevelId
on dbo.yourTable (Name, LevelID)
where DeletedAt in (1, 0);

Unique constraint for table with column for soft delete

3 Answers3