sql violation of unique key constraint

12032
11
Jump to solution
10-03-2017 04:13 PM
AdelaideZumwalt1
Occasional Contributor II

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;

0 Kudos
1 Solution

Accepted Solutions
Asrujit_SenGupta
MVP Regular Contributor

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:

View solution in original post

11 Replies
Asrujit_SenGupta
MVP Regular Contributor

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:

AdelaideZumwalt1
Occasional Contributor II

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?

0 Kudos
Asrujit_SenGupta
MVP Regular Contributor

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.

AdelaideZumwalt1
Occasional Contributor II

 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!!

0 Kudos
AdelaideZumwalt1
Occasional Contributor II

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;

0 Kudos
AdelaideZumwalt1
Occasional Contributor II

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?

0 Kudos
AhmadSALEH1
Occasional Contributor III

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

0 Kudos
AdelaideZumwalt1
Occasional Contributor II

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

0 Kudos
AhmadSALEH1
Occasional Contributor III

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,

0 Kudos