Constraint question

• W F

  Ten Centuries

  Points: 1075

  More actions

  August 25, 2006 at 12:30 pm

  #172208

  Hopefully this is a very easy question.

  I have a table:

  PersonID int,

  GroupID int,

  TestFlag bit

   

  I would like to create a constraint on this table that ensures that no more than 5 people in the same group have the TestFlag set. Is this possible?

• JKSQL

  SSCrazy Eights

  Points: 8481

  More actions

  August 25, 2006 at 2:13 pm

  #656931

  You could use an insert/update Trigger

• W F

  Ten Centuries

  Points: 1075

  More actions

  August 25, 2006 at 2:25 pm

  #656940

  I thought about that, and I know it would work, but something in me rebels at the idea of using triggers for constraint purposes.  I'd like to use a check constraint if at all possible.  I tried one involving a UDF, and it worked for inserts but not updates (besides it was slow as heck).

• Ninja's_RGR'us

  SSC Guru

  Points: 294069

  More actions

  August 25, 2006 at 2:28 pm

  #656942

  Sorry to kill your groove, But you either to that check before the insert in a stored proc or in a trigger.  The simple constraint checks just can't handle that well...

• W F

  Ten Centuries

  Points: 1075

  More actions

  August 25, 2006 at 3:45 pm

  #656960

  I finally got this to work with a constraint/UDF combo, but for some weird reason it doesn't work on Updates using Enterprise manager.  With Query Analyzer the Update is constrained just like it should be.  Might be a bug in EM.