CreateRelationshipClass with MANY_TO_MANY - how?

[nadja]

I'm trying to create several Relationship Classes with the cardinalities 1:1 and n:n. The below script works fine with my 1:1 relationship classes. It runs also for the n:n cardinalities, creates some relationship classes, but I can't access its values with the explore tool as with the 1:1 relationship classes. Why? What's wrong? 

for index, row in df.iterrows():
    arcpy.management.CreateRelationshipClass(origin_table = row["origin_table"],
                                            destination_table = row["destination_table"],
                                            out_relationship_class = row["out_relationship_class"],
                                            relationship_type = row["relationship_type"],
                                            forward_label = row["forward_label"],
                                            backward_label = row["backward_label"],
                                            message_direction = row["message_direction"],
                                            cardinality = row["cardinality"],
                                            attributed = row["attributed"],
                                            origin_primary_key = row["origin_primary_key"],
                                            origin_foreign_key = row["origin_foreign_key"],
                                            destination_primary_key = row["destination_primary_key"],
                                            destination_foreign_key = row["destination_foreign_key"])

with my table:

origin_table

destination_table

out_relationship_class

relationship_type

forward_label

backward_label

message_direction

cardinality

attributed

origin_primary_key

origin_foreign_key

destination_primary_key

destination_foreign_key

mgdm_polygone_polygon

mgdm_polygone_regel

polygon_regel

SIMPLE

Regel

polygon

NONE

MANY_TO_MANY

NONE

Regel

Regel

RegelCode

#

[TonyAlmeida]

ArcGIS creates intermediate relationship table that stores the pairs of related objects. Unlike 1:1 relationships, you cannot directly access related records through the Explore tool without this intermediate table.

Try something like this.

import arcpy
import os

def create_relationship_with_intermediate_check(row):
    """Create relationship class and handle intermediate table for n:n"""
    
    # Create the relationship class
    arcpy.management.CreateRelationshipClass(
        origin_table=row["origin_table"],
        destination_table=row["destination_table"], 
        out_relationship_class=row["out_relationship_class"],
        relationship_type=row["relationship_type"],
        forward_label=row["forward_label"],
        backward_label=row["backward_label"],
        message_direction=row["message_direction"],
        cardinality=row["cardinality"],
        attributed=row["attributed"],
        origin_primary_key=row["origin_primary_key"],
        origin_foreign_key=row["origin_foreign_key"],
        destination_primary_key=row["destination_primary_key"],
        destination_foreign_key=row["destination_foreign_key"]
    )
    
    # Handle n:n specific setup
    if row["cardinality"].upper() == "MANY_TO_MANY":
        handle_many_to_many_relationship(row)

def handle_many_to_many_relationship(row):
    """Locate and verify the intermediate table for an n:n relationship class."""
    
    relationship_class = row["out_relationship_class"]

    print(f"n:n Relationship created: {relationship_class}")

    # Describe the relationship class
    desc = arcpy.Describe(relationship_class)

    # ArcGIS tells us exactly which table is the join table
    rel_table_path = desc.relationshipTable

    if not rel_table_path:
        print("No intermediate relationship table found (Describe returned None).")
        return

    print(f"Intermediate table located: {rel_table_path}")

    # Verify it exists
    if arcpy.Exists(rel_table_path):
        print("Intermediate relationship table verified")

        # Inspect fields
        rel_desc = arcpy.Describe(rel_table_path)
        fields = [f.name for f in rel_desc.fields]
        print(f"Relationship table fields: {fields}")
    else:
        print("Relationship table path does not exist in the workspace.")

[nadja]

Thank you @TonyAlmeida. If I understand your code correctly, this assumes that there already is an intermediate relationship table, correct? how do you create such a table? I found https://pro.arcgis.com/en/pro-app/3.4/tool-reference/data-management/table-to-relationship-class.htm, but this gp tool too assumes there is already such an intermediate table. 

[TonyAlmeida]

Yes, the code assumes the intermediate table already exists.
But ArcGIS automatically creates the intermediate table for you when BOTH of the following are true:

cardinality = "MANY_TO_MANY"

attributed = "NONE" (i.e., NOT attributed)

[nadja]

I get an error if i try to access the relationshipTable from a RelationshipClass using 

print(rc.relationshipTable)

AttributeError: DescribeData: Method relationshipTable does not exist

[TonyAlmeida]

Try this,

Set the parameter values to:

cardinality = "MANY_TO_MANY"
attributed = "NONE"

and

row dictionary contains:
row["cardinality"] = "MANY_TO_MANY"
row["attributed"] = "NONE"

import arcpy
import os

def create_relationship_with_intermediate_check(row):
    """Create relationship class and handle intermediate table for n:n"""

    # Should auto-create the join table
    if row["cardinality"].upper() == "MANY_TO_MANY":
        row["attributed"] = "NONE"  # REQUIRED for system-generated table

    # Create the relationship class
    arcpy.management.CreateRelationshipClass(
        origin_table=row["origin_table"],
        destination_table=row["destination_table"],
        out_relationship_class=row["out_relationship_class"],
        relationship_type=row["relationship_type"],
        forward_label=row["forward_label"],
        backward_label=row["backward_label"],
        message_direction=row["message_direction"],
        cardinality=row["cardinality"],
        attributed=row["attributed"],
        origin_primary_key=row["origin_primary_key"],
        origin_foreign_key=row["origin_foreign_key"],
        destination_primary_key=row["destination_primary_key"],
        destination_foreign_key=row["destination_foreign_key"]
    )

    # Handle M:N after creation
    if row["cardinality"].upper() == "MANY_TO_MANY":
        handle_many_to_many_relationship(row)

def handle_many_to_many_relationship(row):
    """Locate and verify the intermediate table for an n:n (non-attributed) relationship."""

    relationship_class = row["out_relationship_class"]
    print(f"n:n Relationship created: {relationship_class}")

    desc = arcpy.Describe(relationship_class)

    # SAFE ATTRIBUTE CHECK
    if hasattr(desc, "relationshipTable"):
        rel_table_path = desc.relationshipTable
    else:
        print("No system-generated intermediate table exists (attributed=ATTRIBUTED or invalid RC).")
        return

    if not rel_table_path:
        print("Describe returned a blank relationshipTable property.")
        return

    print(f"Intermediate table located: {rel_table_path}")

    if arcpy.Exists(rel_table_path):
        print("Intermediate table verified.")

        # List fields
        fields = [f.name for f in arcpy.ListFields(rel_table_path)]
        print(f"Relationship table fields: {fields}")
    else:
        print("Relationship table does not exist in the workspace.")