topic Inserting record in M:N relationship table "Cannot reset foreign key values for an existing relationship row" in Python Questions

Inserting record in M:N relationship table "Cannot reset foreign key values for an existing relationship row"

DonMorrison1 — Mon, 25 Oct 2021 00:55:17 GMT

For the first time I'm trying to set up and use a many to many relationship. The setup went fine, as expected an "intermediate table" is create the contains two GUID fields. My problem is when I try to use ArcPY to populate that table. It seems that I have to create a new InsertCursor for every record that I want to insert. Does that make sense? This code snippet fails on the second insertRow with "Cannot reset foreign key values for an existing relationship row". However when I uncomment out line 12, it works. I guess I can create the insert cursor every time but I expect the table to get very very large and I seen slow cursor creation on large tables, so it may not be practical.

import os, arcpy DB_BASE_PATH = r'C:\Users\dmorrison\Documents\ArcGIS\Projects\CW Development\CW HUB (SDE).sde' REL_TABLE = os.path.join(DB_BASE_PATH, 'CW_Hub.SDE.TEST_PARCELS_TEST_PARCEL_TO_PARCEL_EVENTS') P_GUID1 = "31D01E3D-F70B-4382-B841-F9DD552C1859" P_GUID2 = "AC8D91AC-59FD-4E90-9451-275EEF6A99E9" E_GUID = "944E481E-4F91-44B3-A640-66383031F8D9" with arcpy.da.InsertCursor(REL_TABLE, ['parcel_guid', 'event_guid']) as cursor: cursor.insertRow([P_GUID1, E_GUID]) #with arcpy.da.InsertCursor(REL_TABLE, ['parcel_guid', 'event_guid']) as cursor: cursor.insertRow([P_GUID2, E_GUID])

ArcGIS Pro 2.8.3

SQL Server 13.0.5888.11 (Windows)

Re: Inserting record in M:N relationship table "Cannot reset foreign key values for an existing relationship row"

Anonymous User — Mon, 25 Oct 2021 16:35:34 GMT

Edit: fixed the code to create cursor in the loop. Probably is still expensive on large tables but it is less code.

Could you loop over a list of the P_GUID's ?

guidList = ["31D01E3D-F70B-4382-B841-F9DD552C1859", "AC8D91AC-59FD-4E90-9451-275EEF6A99E9", p3, ...] E_GUID = "944E481E-4F91-44B3-A640-66383031F8D9" for pguid in guidlist: with arcpy.da.InsertCursor(...) as cursor: cursor.insertRow([pguid, E_GUID])

Re: Inserting record in M:N relationship table "Cannot reset foreign key values for an existing relationship row"

DonMorrison1 — Mon, 25 Oct 2021 18:17:52 GMT

Right, I took it out of the loop simply to demonstrate the problem that I have to create a new InsertCursor for every record that I want to insert. I've used InsertCursors before and never had to do that. There seems to be something different about the many to many intermediate table that causes this.

Re: Inserting record in M:N relationship table "Cannot reset foreign key values for an existing relationship row"

DonMorrison1 — Mon, 15 Nov 2021 01:07:34 GMT

I found an acceptable workaround for this by writing out the rows to insert into a temporary in-memory table then appending the in-memory table to the "intermediate table". At least that way I don't have to create an InsertCursor for every record that I want to add.

Re: Inserting record in M:N relationship table "Cannot reset foreign key values for an existing relationship row"

JasonRoberts — Tue, 15 Apr 2025 22:44:49 GMT

I can confirm this problem still exists in ArcGIS Pro 3.4.0, and the workaround you guys came up with of opening a new cursor for each row you want to insert still works.

Re: Inserting record in M:N relationship table "Cannot reset foreign key values for an existing relationship row"

GKmieliauskas — Tue, 09 Dec 2025 11:51:27 GMT

I can confirm this problem still exists in ArcGIS Pro 3.6.0 and workaround works