1

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?

Improve this question
1
  • The index was not disabled, and the values were exactly the same: 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. Commented Dec 29, 2009 at 17:52

2 Answers 2

Reset to default
5

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

The index was not disabled, and the values were exactly the same: 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.
Repro script and I'll believe you.
SI also important to look at the SET ANSI PADDING setting and the collation under which the SELECT WHERE clause matches the value.
SELECT COUNT(*), PAOClass, Category, PAOName FROM YukonPAOObject HAVING COUNT(*)>1 GROUP BY PAOClass, Catgory, PAOName. Does this return any rows?
0

I'm not a pro on this subject, but the 'is unique'-setting of the index probably refers to the way the index is build/stored, not that it is a constraint. This probably also means the performance of the index is sub-optimal. When creating the index, the DBMS might check this.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.