[SOLVED] GORM many to many with addition field as primary key in join table

GORM many to many with addition field as primary key in join table

Is it possible to add an additional field to the composite primary key created on the join table of a "many to many" relationship?

The reason is for allowing multiple identical records (that differ only in secondary fields, like color for exemple) to be added.

Example:

= record 1 =

Tbl1_ID = "100" (PK)
Tbl2_ID = "200" (PK)
Color = "Red"

= record 2 =

Tbl1_ID = "100" (PK)
Tbl2_ID = "200" (PK)
Color = "Blue"

I can't add these two records because of the PK composed of the Tbl1_ID and Tbl2_ID fields. I think the solution would be to add the ID field of the join table to the composite PK (or the field "Color"). But I don't know how in GORM.

Solution

Ok, after some more reading on the gorm documentation I found I was missing a final step when customizing the join table...

Call SetupJoinTable (with appropriate parameters) before AutoMigrate... So yes, rookie mistake.

If the call of SetupJoinTable is skipped, the join table is created still, the Foreign Keys are added to the join table and a Composite PK is defined that includes de FKs. But the additional PK defined in the model is NOT added to the Composite PK. That was misleading to me.

I hope this will be helpful to others.