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

[DonMorrison1]

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)

[Anonymous User]

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])

[DonMorrison1]

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.

[JasonRoberts]

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.

[DonMorrison1 · Accepted solution]

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.