Unique index allows duplicate values

Question

Post reply

Unique index allows duplicate values

Mathew Leigh

SSC Journeyman

Points: 90
More actions
April 5, 2007 at 10:31 am

#123534

I have a unique index defined on a column, but yet I am able to to insert rows with with duplicate values for that column.
I assumed that the unique index would prevent this.
Am I confused? Is this a bug?

Viewing 7 posts - 1 through 6 (of 6 total)

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

I think it would help if you could post the DDL for the table and index.

Mark Shvarts 1 SSCertifiable Points: 5702 More actions · Answer 2

Is this index enabled ?

You can check this:

select

object_name(object_id),

name,

is_unique,

is_disabled

from sys.indexes

where name = <your_index_name>

Yelena Varshal SSC-Dedicated Points: 34286 More actions · Answer 3

Also check the data, are they really the same? There could be leading or trailing spaces.

Regards,Yelena Varsha

Mathew Leigh SSC Journeyman Points: 90 More actions · Answer 4

Thanks for all replies. We did find the problem. Two unique indexes against the same column instead of against two separate columns.

However, as long as I have everyone's attention in my research I found many references and comparisons of "unique constraints" and "unique indexes" (and whether there was a functional difference, which for the most part there wasn't).

From the SQL Server Management Studio interface I see that you can create a "unique key" (which creates an index within the table definition) or you can create an index that "is unique".

So the terminology to a "unique constraint" confused me. I am guessing that a "unique constraint" is an index which "is unique".

Philip Kelley SSCertifiable Points: 6351 More actions · Answer 5

This one threw me in SQL 2000, and I'd guess it hasn't changed in SLQ 2005. You can:

- Create a unique index

- Create a "unique" constraint on a column (or columns); this is implemented with a unique index

Internally, they are (or were in 2000) configured as different objects types (sysobjects.xtyp), even thought they are essentially the same construct. Ultimately, the only real difference is (1) the code base used to create, update, and maintain them, and (2) relational theory. Properly, you are defining a unique constraint on a column, and that's a logical construct. Techincally, you are creating a unique index, but that's merely physical implementation, and is platform-specific. These days I'd probably go with the unique constraint (same as I use primary keys) rather than declared unique indexes.

Philip

Mathew Leigh SSC Journeyman Points: 90 More actions · Answer 6

Philip, thank you very very much for the reply and the explanation.

-matt