[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?

 

[GravyMonster]

A Constraint can only apply at a column or table level, not at row level.

Therefore it's not possible to do what you're after.

The only way to achieve it by using a constraint is to alter your schema to move the records with TypeID 10 or 11 to their own table and apply the contraint to that.

 

[Dj_Jestar]

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