Post reply

Unique Constraint Problem

  • October 12, 2005 at 5:20 am

    #89480

    Hi,

    I'm using the Visual Studio Server Explorer to make a change to a SQL 2000 table. I want to create a unique constraint across three columns: SampleID, TestID, Active.

    SampleID and TestID are both int type and Active is a bit. I don't have any problem making the unique constraint using the first two (SampleID and TestID) but Active won't appear in the Field dropdown of the Indexes/ Keys tab of the property page. Why is this? Or how do I get around it?

  • October 12, 2005 at 6:22 am

    #596768

    Have no idea why Visual Studio does that.

    The 'proper' way works though... Go into QA and do something like this:

    alter table myTable add constraint myConst unique (SampleID, TestID, Active)

     

    /Kenneth

  • October 12, 2005 at 6:36 am

    #596774

    You're spot on Kenneth!! That worked. I've no idea why VS does that either!

    Thanks again.

    Dec.

  • October 12, 2005 at 7:10 am

    #596783

    VS probably thinks it looks strange to have a unique constraint with only two possible values. Yes, the other columns increase the amount, but for two specific values of those columns you can only have two rows, one with bit column = 1 and the other = 0. There might of course be a real need for this sometimes, so it is possible to add it as Kenneth shows.

    --
    Chris Hedgate http://www.hedgate.net/
    Contributor to the Best of SQL Server Central volumes
    Articles: http://www.sqlservercentral.com/columnists/chedgate/

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

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