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:

Adding a row with Id=1, Date=2018-05-01, Passport=MPEUIE80, Deleted=0
Deleting the row with Id=1, so the field Deleted will be 1
Adding a row with Id=2, Date=2018-05-01, Passport=MPEUIE80, Deleted=0
Deleting the row with Id=2, so the field Deleted will be 1
Adding a row with Id=3, Date=2018-05-01, Passport=MPEUIE80, Deleted=0
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.

``The constraint must allow n records while the Deleted field is 1, but if the Deleted field is 0 there must be one Date and Passport`. Please explain this more clearly — Udit Solanki
– Udit Solanki, Commented May 4, 2018 at 19:14
Kind of smells like something is off in the design if you have one field representing multiple relationships. You can search for something like "Case statement with constraint", but I think a re-evaluation of the structure might be a better option. — Jacob H
– Jacob H, Commented May 4, 2018 at 19:18
You created a bounty but Gordon gave you an answer? What don't you like about the index? — Derrick Moeller
– Derrick Moeller, Commented May 11, 2018 at 15:52

Gordon Linoff · Accepted Answer · 2018-05-15 01:43:06Z

25

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

edited May 15, 2018 at 1:43

answered May 4, 2018 at 19:46

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

WltrRpo Over a year ago

This didn't work for me, error: Incorrect syntax near the keyword 'where'.

Derrick Moeller Over a year ago

@WltrRpo Did you change t to the name of your table?

WltrRpo Over a year ago

@DerrickMoeller yes I did, create unique index unq_t_date_passport on [dbo].[Test] ([date], [passport]) where deleted = 0;

Gordon Linoff Over a year ago

@WltrRpo . . . Filtered indexes were introduced in SQL Server 2008 (learn.microsoft.com/en-us/sql/t-sql/statements/…). If they don't work for you, perhaps you have your compatibility level set to some earlier version (learn.microsoft.com/en-us/sql/t-sql/statements/…).

KnarfaLingus Over a year ago

Shouldnt the 2005 solution be reversed? case when deleted = 1 then id else -1 end, otherwise the unique index is preventing multiple deleted rows - also should handle the date field to be complete

|

Collectives™ on Stack Overflow

SQL Constraint Validate Unique Values

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related