SQL Server Home
Incorrect "Duplicate key" error with unique filtered index
5/30/2009 3:56:46 AM
User(s) can reproduce this bug
When updating a table with a unique filtered, the "Cannot insert duplicate key row" error appears, even if the final state of the updated table does not contain duplicates. In this case, SQL Server behaves as if the rows were updated one at a time, instead of behaving as if the rows were logically updated simultaneously (like the SQL standard assumes).
In a similar case involving normal indexes, the error is avoided by splitting the index update operation in two operations (delete and insert), which is what it should be done here, too.
SQL Server MVP
SQL Server 2008 SP1
Windows Vista 64-bit SP1
Operating System Language
Steps to Reproduce
CREATE TABLE T1(x int PRIMARY KEY, y int, z bit)
INSERT INTO T1 VALUES (1,1,0), (2,1,0), (3,1,1), (4,2,0), (5,2,0), (6,2,1), (7,3,0), (8,3,0), (9,3,1)
CREATE UNIQUE INDEX I ON T1(y) WHERE z=1
SELECT x,y FROM T1 WHERE z=1
UPDATE T1 SET z=CASE WHEN x%3=1 THEN 1 ELSE 0 END -- error
DROP INDEX I ON T1
UPDATE T1 SET z=CASE WHEN x%3=1 THEN 1 ELSE 0 END -- ok
CREATE UNIQUE INDEX I ON T1(y) WHERE z=1 -- ok
SELECT x,y FROM T1 WHERE z=1
DROP TABLE T1
CREATE TABLE T2(x int PRIMARY KEY, y int)
CREATE UNIQUE INDEX I ON T2(y)
INSERT INTO T2 VALUES (0,0), (1,1)
UPDATE T2 SET y=1-y
SELECT * FROM T2
DROP TABLE T2
When the unique filter exists on table T1, the UPDATE statement fails with the error "Cannot insert duplicate key row in object 'dbo.T1' with unique index 'I'." However, if the index is dropped and then recreated, the UPDATE is executed successfully.
On table T2 (with a normal unique index), the UPDATE statement works fine, because the index update operation is split in two operations (delete and insert).
The UPDATE should execute successfully if the final state is valid (the filtered index update operation should be split in two operations: delete and insert).
to post a comment.
Please enter a comment.
on 12/12/2012 at 8:32 AM
I have this problem with SQL Server 2008 R2 64bit (SP1 and SP2). I thought it was because I was using SP1 but after upgrading to SP2 I am still getting the issue. My current build is:Microsoft SQL Server 2008 R2 (SP2) - 10.50.4000.0 (X64)
Where I am seeing the issue and where it is causing me major problems is:
1) I created a unique filtered index on a nullable column that used in a foreign key relationship
2) I then “Design” the table in management studio and add a new column.
3) Click save in the designer window and I get the “Duplicate Key” error
From looking at the change script that management studio generates it is dropping the index and recreating it once the new table has been built. However when recreating the index the filter is missing from the create index syntax which makes the action fail because I have null values in this column in my table. This is annoying but not massively damaging.
What IS massively damaging is that the action is not rolled back correctly and all of the foreign keys on the table are dropped and have to be manually recreated.
The syntax for creating the index is:
CREATE UNIQUE NONCLUSTERED INDEX [UNIX_IndexA] ON [SchemaA].[TableA] ([ColumnA] ) WHERE ([ColumnA] IS NOT NULL)
Is there a fix for this as it is a massive problem and is stopping me use unique filtered indexes?
on 11/13/2010 at 10:46 AM
The problem is fixed in SQL Server 2008 SP2 (build 10.0.4000), but is not fixed in SQL Server 2008 R2 CU4 (build 10.50.1746).
on 8/5/2010 at 10:04 AM
Has this problem been corrected? If so, where can I get exactly what fix? (relatively urgent)
on 6/1/2010 at 10:50 AM
Thank you for reporting this issue. This issue has been fixed and is scheduled to be released in an upcoming SQL Server 2008 PCU (Public Cumulative Update).
on 6/3/2009 at 4:05 PM
Thanks for reporting this issue to us. I can confirm it is an product issue, and we are currently expecting to fix it in SQL Server 2008 Service Pack 2.
on 5/31/2009 at 10:41 AM
Erland Sommarskog observed that the split operator is used correctly when the indexed column is changed, but the bug is that the split operator is not used when columns in the filter condition are updated.
Considering this, I have added a workaround (which is usually not feasible, but should be known anyway).
to post a workaround.
Please enter a workaround.
on 5/31/2009 at 10:40 AM
The UPDATE statement can be changed to:
UPDATE T1 SET y=y+0, z=CASE WHEN x%3=1 THEN 1 ELSE 0 END
Not a very good workaround, because the sometimes the queries cannot be dictated by the database developer, but it is interesting to know anyway.
© 2014 Microsoft