How to ignore unique Index on multiple columns based on column value while inserting new record

Question

We have a table with three columns, StudentId and SubjectId and Active (and a few other columns but not related to this question).

Active column indicates whether the record is active or not(we set this Active column to zero if someone deletes the records from UI)

Definition of the index on columns StudentId and SubjectId is as below:

CREATE UNIQUE NONCLUSTERED INDEX [UQ_StudentSubject_SubjectId_StudentId]  
ON [dbo].[StudentSubject]
(
    [StudentId] ASC,
    [SubjectId] ASC

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

GO

From our application, if we try to insert another record with the combination of this SubjectId and studentId the insert is failing and Java is throwing below error:

Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Cannot insert duplicate key row in object 'dbo.StudentSubject' with unique index 'UQ_StudentSubject_SubjectId_StudentId'. The duplicate key value is (113460, 182).

The record with combination 113460, 182 is having Active as zero so, we are trying to insert new record instead of setting Active flag to 1.

Is there any way we can ignore this index while inserting if we insert a new record with the existing combination of subjectId and studentId have Active column as zero?

EDIT Sorry for the confusion, It is an index NOT a constraint.

Not with a unique constraint, but you can create a unique filtered index specifying `WHERE Active = 0`. — Dan Guzman, Sep 30 '15 at 01:11
We do not want to add active column to the index, the problem is if we inactive this column in future there will be two columns having same combination so the update may fail. @FelixPamittan — MaheshVarma, Sep 30 '15 at 01:16
I have no idea on filtered index. will google it. @FelixPamittan — MaheshVarma, Sep 30 '15 at 01:20

score 7 · Accepted Answer · answered Sep 30 '15 at 01:25

7

Below is an example of a filtered index that will ignore rows with Active value 0:

CREATE UNIQUE NONCLUSTERED INDEX UQ_StudentSubject_SubjectId_StudentId  
ON dbo.StudentSubject
(
    [StudentId],
    [SubjectId]
) WHERE Active <> 0;

answered Sep 30 '15 at 01:25

Dan Guzman

43,250
3
46
71

Awesome! this is what I was looking for. – MaheshVarma Sep 30 '15 at 01:30
Is there any way to define such an index via EF code first approach? – Raj Apr 08 '18 at 04:16
1

@Raj, it looks like you'll need to use a DbMigration class with EF code first as described in this [answer](https://stackoverflow.com/questions/32894547/entity-framework-filter-index?utm_medium=organic&utm_source=google_rich_qa&utm_campaign=google_rich_qa). – Dan Guzman Apr 08 '18 at 11:03

score 1 · Answer 2 · answered Sep 30 '15 at 18:11

You may not want to add Active to the index, but if you want to have two (or more) versions of (studentID, subjectID), differing only by the value of Active, then you've pretty much designed yourself into that corner. If you need to have more than one row with a particular combination of StudentID and SubjectID, you're going to have to change the design. Sorry.

One option, if it is available to you, is to add a row "unique-ifier" by way of a timestamp.

create table StudentSubject(
    StudentID   int not null,
    SubjectID   int not null,
    ChangeDate  datetime2 default GetDate(),
    Active      int default 1,
    ...,
    constraint  PK_StudentSubject primary key( StudentID, SubjectID, ChangeDate )
);

Now you have an infinitely scalable solution -- any particular pairing of student and subject may have any number of Active settings.

StudentID SubjectID ChangeDate Active ...
      100        42 2015-01-01      1 ... -- first entry
      100        42 2015-01-03      0 ... -- nope, deactivate this entry
      100        42 2015-01-05      1 ... -- changed my mind, reactivate
      100        42 2015-01-07      0 ... -- horoscope indicates bad idea
      100        42 2015-01-09      1 ... -- wait, I'm not superstitious, go for it!

To obtain the current Active setting, simply get the record with the most recent ChangeDate value:

select  ss.StudentID, ss.SubjectID, ss.ChangeDate, ss.Active
from    StudentSubject  ss
where   ss.StudentID = @Stud
    and ss.SubjectID = @Subj
    and ss.ChangeDate =(
        select  Max( ss1.ChangeDate )
        from    StudentSubject  ss1
        where   ss1.StudentID = ss.StudentID
            and ss1.SubjectID = ss.SubjectID );

Don't let the subquery concern you. Notice it only uses the clustered index.

Here are a couple of added benefits. Suppose you are asked, "What was the Active value of student 100 and subject 42 as of 2015-01-04?" Fine, you just add this line to the end of the query, as part of the subquery:

            and ss1.ChangeDate <= @AsOfDate );

and set @AsOfDate to the date you want to see.

This provides you with a "lookback" query to see what the data looked like at any particular time. In fact, setting @AsOfDate to GetDate() will return the current state, so you can use the same query for "current" and "lookback" queries.

Another possible benefit: say you know in advance that an Active value will change. Today is 2015-01-10 and the row is scheduled to go inactive on 2015-01-15. Go ahead and insert the row with the scheduled date:

      100        42 2015-01-15      0 ...

The "current" query will continue to show the Active value as 1 until the 15th, when the value will show as 0.

A view which shows only the current version makes that data available to the app so it doesn't even have to know about the structure. A trigger on the view will make it possible for the app to query and perform DML on one object providing you with a layer of abstraction between the app and the data.

How to ignore unique Index on multiple columns based on column value while inserting new record

2 Answers2