May 2, 2003 at 8:46 am
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
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