SQL Constraint Validate Unique Values

Question

I have a table and I need to restrict the data inserted, I already use a trigger but I was wondering if I could do the same task with a constraint.

The fields are:

Id
Date
Passport
...
Deleted

The constraint must allow n records while the Deleted field is 1, but if the Deleted field is 0 there must be only one row with the same Date and Passport.

Should work for this steps:

1. Adding a row with Id=1, Date=2018-05-01, Passport=MPEUIE80, Deleted=0
2. Deleting the row with Id=1, so the field Deleted will be 1
3. Adding a row with Id=2, Date=2018-05-01, Passport=MPEUIE80, Deleted=0
4. Deleting the row with Id=2, so the field Deleted will be 1
5. Adding a row with Id=3, Date=2018-05-01, Passport=MPEUIE80, Deleted=0
6. Adding a row with Id=4, Date=2018-05-01, Passport=MPEUIE80, Deleted=0

Till the fifth step, everything works, but in the last step there must be an error, that is because I can't handle two rows with the same Date, same Passport and both with Deleted=0

Thanks in advance.

Answer 1

You can use a filtered, unique index:

create table [t](
    [id] [int] NULL,
    [date] [datetime] NULL,
    [passport] [char](8) NULL,
    [deleted] [int] NULL
)

create unique index unq_t_date_passport on t(date, passport)
    where deleted = 0;

EDIT:

If filtered indexes are not working on your version of SQL Server, then you might want to check the compatibility level. It should be available whenever the compatibility is set to 100 or higher. You can check your server version and compatibility level using:

SELECT SERVERPROPERTY('ProductVersion');
SELECT name, compatibility_level FROM sys.databases;

There is another method that will work starting in SQL Server 2005, assuming the id is unique and non-negative. It uses a computed column to do essentially the same thing as the filtered index:

alter table t
    add column deleted_id (case when deleted = 0 then -1 else id end) persisted;

create unique index unq_t_passport_date_deleted_id on t(passport, date, deleted_id);