CODEWITHSUNDEEP
sql-serverdatabase-design
g_b
g_b

Reputation: 12438

Unique constraint for table with column for soft delete

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.

Upvotes: 4

Views: 3894

Answers (3)

cdev
cdev

Reputation: 5381

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);

Upvotes: 0

Rohan Shenoy
Rohan Shenoy

Reputation: 895

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]

Upvotes: 0

Ben Thul
Ben Thul

Reputation: 32737

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.

Upvotes: 5

Related Questions