MS-SQL content-aware unique constraint for multiple columns possible?

Question

I'm building process engine and want to ensure only single process instance is pending or active at given time for the given case. However, identical process can be started once previous instance finishes. I would like to use database constraint for maximum reliability with unrealiable networks and lost responses.

I have table with following columns

• process_id (PRIMARY KEY)
• case_id (FOREIGN KEY)
• state (PENDING | ACTIVE | COMPLETED)

Multiple rows with same case_id and state COMPLETED is allowed for restarts. However, I would like to prevent identical rows with state PENDING or ACTIVE to enforce my requirement of single process instance at time.

Here is example of allowed scenario with identical cases having state COMPLETED

process_id    case_id    state
------------------------------
1             1          COMPLETED
2             1          COMPLETED <-- case_id duplicate allowed for multiple COMPLETED
3             1          PENDING

Here is example of disallowed scenario with identical cases having state PENDING

process_id    case_id    state
------------------------------
1             1          COMPLETED
2             1          COMPLETED
3             1          PENDING
4             1          PENDING   <-- case_id duplicate not allowed for multiple PENDING!

And here is example of disallowed scenario with identical cases having combination of states PENDING and ACTIVE

process_id    case_id    state
------------------------------
1             1          COMPLETED
2             1          COMPLETED
3             1          ACTIVE
4             1          PENDING   <-- case_id duplicate not allowed for combination of ACTIVE and PENDING!

Is this kind of content-aware unique constraint possible in MS-SQL?

Answer 1

Based on the sample, it seems that you just need a filtered UNIQUE INDEX on case_id where state does not have a value of 'COMPLETED':

CREATE UNIQUE INDEX UQ_IncompleteCases ON dbo.YourTable (cast_id)
WHERE state != 'COMPLETED';

This will allow any number of rows, for a single case_id, where state value has 'COMPLETED' but will only allow one for a single case_id for all other values of state.

Note that if state is NULLable, you may need to add logic to include/exclude those in the above WHERE.