topic Re: sql violation of unique key constraint in Data Management Questions

sql violation of unique key constraint

AdelaideZumwalt1 — Tue, 03 Oct 2017 23:13:58 GMT

When I run the following script, which has run successfully for me on a number of other tables I get the following error:

Violation of UNIQUE KEY constraint 'versions_uk'. Cannot insert duplicate key in object 'dbo.SDE_versions'. The duplicate key value is (Poles, DBO).

The primary key for the Poles table is the ObjectID.

EXEC dbo.create_version 'dbo.DEFAULT', 'Poles', 1, 1, 'This version contains data pushed from WMM to ESRI';
EXEC dbo.set_current_version 'Poles';
EXEC dbo.edit_version 'Poles', 1;
MERGE ElectricNetwork.dbo.POLE_evw
USING WMM_Export.dbo.Pole
ON ElectricNetwork.dbo.POLE_evw.esElementName = WMM_Export.dbo.Pole.wmElementName
WHEN MATCHED THEN
UPDATE SET ElectricNetwork.dbo.POLE_evw.GUID_WMM= WMM_Export.dbo.Pole.wm_ElementGuid;
EXEC dbo.edit_version 'Poles', 2;

Re: sql violation of unique key constraint

Asrujit_SenGupta — Wed, 04 Oct 2017 09:11:50 GMT

Edit versioned data in SQL Server using SQL—Help | ArcGIS Desktop

The error or you may say the Warning is due the creation of the Version named 'Poles' as there is already a Version with the same name present in the geodatabase

EXEC dbo.create_version 'dbo.DEFAULT', 'Poles', 1, 1, 'This version contains data pushed from WMM to ESRI';
<name_rule> indicates whether the name specified for the version should be taken as given (2), or a unique name should be created when a duplicate name is specified (1).

Even though you get that error\warning, a new Version is created in the Geodatabase. Check the screenshot below:

Re: sql violation of unique key constraint

AdelaideZumwalt1 — Wed, 04 Oct 2017 15:06:40 GMT

Yes, that appeared to be the problem. Thank you! Maybe you can help me with my next question too. Once I deleted the existing versions and re-ran it I got this error:

Invalid object name 'ElectricNetwork.dbo.POLE_evw' The object name is correct and named correctly. Is the "_evw" part incorrect?

Re: sql violation of unique key constraint

Asrujit_SenGupta — Wed, 04 Oct 2017 15:09:50 GMT

Is the POLE feature class registered as versioned? If yes, confirm the Versioned View name from SQL Server Management Studio or in Feature Class properties in ArcCatalog.

Re: sql violation of unique key constraint

AdelaideZumwalt1 — Wed, 04 Oct 2017 15:22:52 GMT

I found the issue... for some reason poles is plural when versioned (This must be because I changed the feature class name to singular after registering it as versioned. Thank you!!

Re: sql violation of unique key constraint

AdelaideZumwalt1 — Wed, 04 Oct 2017 15:41:00 GMT

Another question, it ran fine on a couple of my tables, but on another I got this error: Violation of PRIMARY KEY constraint 'a26_rowid_ix1' Cannot insert duplicate key in object 'dbo.a26.' The duplicate key value is '1172404, 658'.... The OBJECTID is the primary key in the sql table and I'm not using it as part of the join...

EXEC dbo.create_version 'dbo.DEFAULT', 'Assembliesb', 1, 1, 'This version contains data pushed from WMM to ESRI';
EXEC dbo.set_current_version 'Assembliesb';
EXEC dbo.edit_version 'Assembliesb', 1;
MERGE ElectricNetwork.dbo.Assemblies_evw
USING ElectricNetwork.dbo.Poles_evw
ON ElectricNetwork.dbo.Assemblies_evw.MapPointName = ElectricNetwork.dbo.Poles_evw.esElementName
WHEN MATCHED THEN
UPDATE SET ElectricNetwork.dbo.Assemblies_evw.GUID= ElectricNetwork.dbo.Poles_evw.GlobalID;
EXEC dbo.edit_version 'Assembliesb', 2;

Re: sql violation of unique key constraint

AdelaideZumwalt1 — Wed, 04 Oct 2017 15:50:11 GMT

I'm guessing it's because there is a duplicate "ElementName" somewhere in the table that is tripping it up. Is there any way to bypass this? Or skip that particular record and move on without crashing the entire script?

Re: sql violation of unique key constraint

AhmadSALEH1 — Thu, 20 Sep 2018 19:23:08 GMT

Asrujit SenGupta,

I am getting this error from ArcMAP when I try to reconcile my edits! also I noticed that the labels in "NodesLocation" feature class -which the error references- are duplicated but the features are not !

what might be the source of this issue, I am just doing simple edits on the features from ArcMAP.

Geodatabase version is 10.5.0

ArcMAP 10.5.0

Thank you,

Ahmad

Re: sql violation of unique key constraint

AdelaideZumwalt1 — Thu, 20 Sep 2018 20:15:44 GMT

How did you create your version? Was it using a script or through ArcMap? Also - how old is your version?

Re: sql violation of unique key constraint

Asrujit_SenGupta — Thu, 20 Sep 2018 20:26:12 GMT

You need to run the Diagnose and Repair tools to fix this:

Diagnose Version Tables—Help | ArcGIS Desktop

Repair Version Tables—Help | ArcGIS Desktop

Remember to backup before running the tools.

Re: sql violation of unique key constraint

AhmadSALEH1 — Fri, 21 Sep 2018 17:05:36 GMT

Thanks Asrujit,

Usually I do run those 2 tools, sometimes I get that there is multiversioned tables had orphaned and sometimes I get nothing orphaned.

this issue keeps happening all the day and its hard to keep disconnecting all users to apply diagnose and repair. I am looking for a method to prevent this from happening. any ideas?

Thank you,

Ahmad

Re: sql violation of unique key constraint

AhmadSALEH1 — Fri, 21 Sep 2018 17:08:03 GMT

Adelaide,

My versions are created from ArcMap, and most of them are less that a day, from my testing I found that even if you created the version when you start working (less than 1 hour age) you will still get this error!

Thank you,