SQL Server unique index allowing duplicates

views:

122

answers:

SQL Server unique index allowing duplicates

I am using SQL Server 2008, had a table with an id (numeric) column as the primary key. Also had a unique index on three varchar columns. I was able to add a row with the exact same set of the three columns. I verified it with a simple query on the values and 2 rows were returned.

I edited the index and added the id column. When I tried to edit it again and remove the id column it complained that there were duplicate rows, it deleted the index but couldn't create it.

I then clean the database of the duplicated, recreated the index with the same 3 varchars as unique and nonclustered and now it works properly, not allowing duplicates.

Does anyone know why the uniqueness of this index was ignored?

+5 A:

The index could had been disabled (see Disabling Indexes), your 'duplicate' values may had been different (trailing spaces for example), or your test may be incorrect.

For sure you did not insert a duplicate in a enforced unique index.

Remus Rusanu 2009-12-28 22:10:32

The index was not disabled, and the values were exactly the same: http://atsolberg.com/dupPaos.JPG Here's a pic showing the properties of the index and the query + results of two rows returned. I also added len(***)'s in the select to ensure the varchars values were the same length. Also these rows are not old rows add before the index existed, they were just added.

2009-12-29 17:51:59

Repro script and I'll believe you.

Remus Rusanu 2009-12-29 17:55:57

SI also important to look at the SET ANSI PADDING setting and the collation under which the SELECT WHERE clause matches the value.

Remus Rusanu 2009-12-29 18:08:07

`SELECT COUNT(*), PAOClass, Category, PAOName FROM YukonPAOObject HAVING COUNT(*)>1 GROUP BY PAOClass, Catgory, PAOName`. Does this return any rows?

Remus Rusanu 2009-12-29 18:10:11

ansaurus

tags:

views:

answers:

SQL Server unique index allowing duplicates

related questions