[MSSQL] "Where" clause on unique constraint?

[Dj_Jestar]

Hi,

We have a table for Milestones, and of these milestones we need to constrain that for particular types of milestone, only one can exist per job.. However, for other types, many can exist.

Probably better explained in code (that doesn't work):

ALTER TABLE Milestones
  ADD CONSTRAINT Milestones_Unique UNIQUE (TypeId, JobId)
  WHERE TypeId IN (10, 11);

Can anyone offer some help?

 

[Dj_Jestar]

There was a way to do it, to use a Check constraint with a UDF.