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.
3条答案
按热度按时间yk9xbfzb1#
In my case, I was doing
The thing is that
SELECT ... FROMreturned more than one row. Geeztpxzln5u2#
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
cyvaqqii3#
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.