duplicate key

Question

Post reply

duplicate key

raym

SSCrazy

Points: 2213
More actions
July 29, 2008 at 9:34 am

#188808

I create a unique index using all keys in the table to ignore duplicates.
Now when I run updates I get the following error ?
update Test set CardNo = null where ISNUMERIC(CardNo) <> 1
update Test set Accountno = null where ISNUMERIC(Accountno) <> 1
Msg 2601, Level 14, State 1, Line 1
Cannot insert duplicate key row in object 'dbo.Test' with unique index 'uq_idx_XG_Test_CardNo'.
The statement has been terminated.
CREATE UNIQUE INDEX uq_idx_XG_Test_CardNo
ON Test
( CardNo
,Base
,TxType
,RspCode
,RspFault
,TxDate
,TxCurrency
,TxAmount
,SettlementCurrency
,SettleAmount
,Acquirer
,TerminalID
,TraceNr
,Merchant
,Accountno
,Rundate)
WITH ignore_dup_key

Viewing 4 posts - 1 through 3 (of 3 total)

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

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

What do you get when you run this, Ray?

SELECT COUNT(*), CardNo,Base,TxType,RspCode,RspFault,TxDate,TxCurrency,TxAmount,SettlementCurrency,SettleAmount,

Acquirer,TerminalID,TraceNr,Merchant,Accountno,Rundate

FROM Test

WHERE ISNUMERIC(Accountno) <> 1

GROUP BY CardNo,Base,TxType,RspCode,RspFault,TxDate,TxCurrency,TxAmount,SettlementCurrency,SettleAmount,

Acquirer,TerminalID,TraceNr,Merchant,Accountno,Rundate

HAVING COUNT(*) > 1

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Grant Fritchey SSC Guru Points: 398890 More actions · Answer 2

You can only have one NULL value in those columns with the constraint.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Jack Corbett SSC Guru Points: 184394 More actions · Answer 3

The With IGNORE_DUP_KEY only ignores duplicates on insert operations. From BOL:

IGNORE_DUP_KEY = { ON | OFF }
Specifies the error response to duplicate key values in a multiple-row insert operation on a unique clustered or unique nonclustered index. The default is OFF.
ON
A warning message is issued and only the rows violating the unique index fail.
OFF
An error message is issued and the entire INSERT transaction is rolled back.
The IGNORE_DUP_KEY setting applies only to insert operations that occur after the index is created or rebuilt. The setting has no effect during the index creation operation.

So your update should fail by design.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question