BUG: BOL Incorrectly States That IGNORE_DUP_KEY Affects UPDATE Statements (301113)


The information in this article applies to:

  • Microsoft SQL Server 2000 (all editions)
This article was previously published under Q301113
BUG #: 354426 (SHILOH_BUGS)

SYMPTOMS

The "Creating a Unique Index" section of SQL Server Books Online (BOL) incorrectly states that the IGNORE_DUP_KEY option can affect UPDATE statements:

"When you create or modify a unique index, you can set an option to ignore duplicate keys. If this option is set and you attempt to create duplicate keys by adding or updating data that affects multiple rows (with the INSERT or UPDATE statement), the row that causes the duplicates is not added or, in the case of an update, discarded."

This is incorrect. If you attempt an UPDATE statement that would result in duplicate rows, the entire UPDATE statement will be rolled back.

STATUS

Microsoft has confirmed this to be a problem in the SQL Server 2000 documentation.

MORE INFORMATION

The following script shows that when IGNORE_DUP_KEY is used, an INSERT statement discards only the rows that would be duplicates, whereas the entire UPDATE statement is rolled back. 
USE tempdb
GO
DROP TABLE test1
DROP TABLE test2
go
CREATE TABLE test1 (col1 CHAR(5), col2 INT)
CREATE TABLE test2 (col1 CHAR(5), col2 INT)
go
INSERT test1 VALUES('one', 1)
INSERT test1 VALUES('two', 2)
INSERT test1 VALUES('three', 3)
INSERT test2 VALUES('four', 2)
INSERT test2 VALUES('three', 1)
go
CREATE UNIQUE INDEX index1 ON test1(col1) WITH IGNORE_DUP_KEY
CREATE UNIQUE INDEX index2 ON test2(col1)
go
SELECT * FROM test1
SELECT * FROM test2
go
UPDATE test1 SET test1.col1=test2.col1 FROM test1 INNER JOIN test2 ON
test1.col2=test2.col2
SELECT * FROM test1
GO
INSERT test1 SELECT * FROM test2
GO
SELECT * FROM test1
GO
Modification Type:MajorLast Reviewed:10/3/2003
Keywords:kbbug kbpending KB301113
©2003 Microsoft Corporation. All rights reserved.