Post reply

Constraint question

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

  • August 25, 2006 at 2:13 pm

    #656931

    You could use an insert/update Trigger

  • 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).

  • 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...

  • 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.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply