My schema has 3 columns: ID1, ID2, Status
Each of the above columns is a string.
I would like to create a constraint which is the following:
There cannot be multiple records which have the same ID1 and ID2 which are in the 'UNPROCESSED' state. It is ok if there are multiple records which have the same ID1 and ID2 which are not in the UNPROCESSED state.
Is it possible to do this in SQL Server?
Assuming you're using SQL Server 2008 or later, you can apply a filtered index:
CREATE UNIQUE INDEX UQ_Unprocessed_IDs ON UnnamedTable (ID1,ID2)
WHERE (Status='Unprocessed')
I don't believe you can do that with a constraint. You need to implement a trigger on insert/update operations. The problem with SQL Server is that triggers are 'AFTER' triggers. There's no such thing as a 'BEFORE' trigger (though there is an 'INSTEAD OF' trigger type.
Hence, you need to do all the work to perform the transaction, vet it and roll it back if the constraint fails, rather than simply checking to see if the transaction would cause the constraint to be violated.
I would do something like having a column called ProcessedId (instead of Status) and assign values based on the ID1 and ID2 being processed or not. To clarify, see below
[ID1, ID2, ProcessId]
SomeId1, SomeId2, -1
SomeOtherId1, SomeOtherId2, 100304
So any unprocessed set of Ids will always have a ProcessId of -1, blocking and duplicates and any PROCESSED set of Id's will be assigned some sort of sequential number, allowing duplicates. Make sense?
So continuing on with my above example, if record set came in again unprocessed it would have a ProcessId of -1 and cause a PK violation.
You can do this using an view that is a union of two underlying tables, one for processed records, one for unprocessed reports.
However, the view itself cannot be made updatable, and if records ever change from processed back to unprocessed, and do so very often, this will perform poorly.
It will also make your thinking about parallel processing scenarios more complex.
Both of the limitations above are because you are replacing some updates by delete+insert.
A filtered index is typically a better solution if you have at least SQL Server 2008.
