Violation of primary key constraint error when no duplicate exists in target

Question

I get a violation of primary key constraint error on a single record when I try to insert it to an existing table.

The first time the issue appeared was within a merge statement, but I can reproduce the issue trying to insert the record alone through .

The primary key is on a varbinary(20) value generated from a hashbytes function on the concatenation of the values of approximately 20 column values. The varbinary(20) value I'm trying to insert is not present in the target table and it is only present once in the source table. When I filter on the actual column values, the result is the same; one record in the source and zero records in the target.

The target table has 101 178 878 rows. The primary key is not the clustered index on the table, there is an bigint identity column serving as the clustered index. There are no triggers on the target or the source. The collations are the same all over (Danish_Norwegian_CI_AS). The instance is a SQL Server 2008 R2, Standard Edition.

I'm now totally stuck, and I hope very much someone has some help to offer.

Thank you for reading :-)

This is the value that fails: 0x0571F5F713CF220BB0DB057BBCE7E158CA6F89C0

In the target, the following values are the closest to the one failing. There is no value equal to the one failing.

0x0571F5F1238704E1A9D612F0A1648766B769E2AE 0x0571F5F713CF220BB0DB057BBCE7E158CA6F89C0 --the value that fails 0x0571F608CBE12A007F3DC3736D2F03D90ECCF3F7

EDIT: There are corruption issues in the database causing the unexpected behaviour.

Tarver

Could you post your insert statement? If you run its select part as a separate query, does it return one row? – Agma

Are there any triggers on this table that are performing subsequent INSERTs in the background? If so, it may be the subsequent INSERT and not this one that is failing. – Garth

@dazedandconfused: OP states there are no triggers – Susurrus

I can't post insert statement, but it is very simple insert into target (col1, col2, etc) select col1, col2 from source. There are no triggers on the table. – Tarver

Whoops, missed that. Sorry. – Garth

Have you tried joining source and target on that column? Does it return rows? – Ashil

If you can't post the insert statement, how do you expect us to help you? I'm voting to close this question. – Agma

I have tried joining, it does not return any rows. I find this strange, as the original merge statement returning the isse is joining on the same column. I can't show the column or table names due to security issues with client, but the insert statement is straight foreward as I showed above: insert into <targettable> (hashpk, col1, col2, col3) select haskpk, col1, col2, col3 from <sourcetable> The source and target tables have the same columns with the same data types. – Tarver

My guess is back on triggers. Can you, please, double-check if there aren't any? :) SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID('TargetTableName') – Ashil

And yes, the select part of the insert returns one row. – Tarver

No, there are no triggers. This is a datawarehouse and there are no triggers in the database at all ;-) – Tarver

Is there some implicit conversion between targettable.hashpk and sourcetable.hashpk? – Audsley

So it seems I'm dealing with corruption. Oh yeah, lucky me! Thank you all for trying to help. It is highly appreciated. – Tarver

Have you tried to Rebuild the index and shrink DB ? – Circuitous

Answer 1

In my case, I was doing

INSERT INTO...
SELECT ... FROM

The thing is that SELECT ... FROM returned more than one row. Geez

Illhumored

Answer 2

Well your answer is definately in the error, To find the cause I would do (inside of a transaction with a rollback)

Try doing a merge with an update and an insert, and not jsut an insert . With the merge in the OUTPUT section you generate the code that states what you are actually doing, an update or an insert. the sysntax for merge if you need it

if you get a update and 0 rows affected you are fighting a loosing bettel against your corruption issues.

If the coruption is in the statistics then script out the indexes, drop them, drop the statistics that still remain, generate the indexes again and see if you still have the issue.

Hope to have been of help

Walter

Guglielma

Answer 3

I faced this weird problem today. An user was trying to register to our website but could not do that because the UserName was violating unique key constraint. When I searched in the database, I could not find that user name already exists. So, after a painful investigation, I found that the column "UserName" in my table was of "nvarchar(X)" type. Thats why whenever there was a foreign or special character, the regular query with WHERE clause could not show me that the user already exists. Then I changed the table's column type from nvarchar(x) to varchar(x), the problem was solved. I could see that there was duplicate user name already exists.

Mallis