Post reply

Unique constraint causes rows not to be displayed

  • May 2, 2003 at 8:46 am

    #81890

    OK, heres my problem, I have a table that uses the following 2 constraints:

    CONSTRAINT [UNIT_COMMS_DVC_TYPE_PK] PRIMARY KEY CLUSTERED

    (

    [UNIT_COMMS_DVC_TYPE_ID]

    ) ON [PRIMARY]

    CONSTRAINT [IX_UNIT_COMMS_DVC_TYPE] UNIQUE NONCLUSTERED

    (

    [UNIT_ID],

    [COMMS_DVC_TYPE_ID]

    ) ON [PRIMARY]

    The problem is that when I use the following sql in an SP:

    SELECT DISTINCT UNIT_COMMS_DVC_TYPE_ID,

    UNIT_ID, COMMS_DVC_TYPE_ID

    WHERE UNIT_ID = @UNIT_ID

    it only displays 1 record even though there are 2 that should match, however, when I remove the second constraint from my table both records then show up. Is there something I'm missing here?

    Edited by - matthewkane on 05/02/2003 08:46:52 AM

  • May 5, 2003 at 8:00 am

    This was removed by the editor as SPAM

  • May 16, 2003 at 1:05 am

    #457418

    Perhaps the constraints (especially the second) were create WITH NOCHECK.

    Because you can bypass checking when the constraint is created, it is possible (I think) to create a unique constraint on a key that is not unique. This would automatically create an index which is assumed to be unique. Then the optimiser chooses the "unique" (not really) index and gets only one row even though it is not really unique.

    This is just a stab in the dark.

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

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