Foreign key constraints not generated when models are in incorrect order (many-to-many)

[3mm4nuel]

When UserRole mapping table model is last, foreign key constraints are not generated

However, when UserRole is not last (i.e. first or second), fk constraints are generated

[noamcattan]

Are you able to provide the model definitions?

[3mm4nuel]

Are you able to provide the model definitions?

[ronenlu]

Hi @3mm4nuel There is a bug in GORM, so if you defined Customize JoinTable, you should pass it before the structs that has the many to many annotation.

[jeandeducla]

Hello! I had the same issue and hopefully I found this thread. However I cannot seem to make the use case fully work.

The Gorm Customize JoinTable documentation seems to say that you can add extra columns to the join table i.e. columns that are not the many2many FK however when I add extra columns and generate the migration, those columns are not generated. If I take the above example i.e. those models:

type User struct {                                                                                                                                 
    ID    string  `gorm:"primarykey;column:id;"`                                
    Name  string  `gorm:"column:name;"`                                
    Roles []*Role `gorm:"many2many:UserRole;foreignKey:id;references:id;constraint:OnUpdate:CASCADE,OnDelete:CASCADE"`
}                                                           

type Role struct {                                                                               
    ID          string  `gorm:"primarykey;column:id;"`                                
    Description string  `gorm:"column:description;"`                                
    Users       []*User `gorm:"many2many:UserRole;foreignKey:id;references:id;constraint:OnUpdate:CASCADE,OnDelete:CASCADE"`
}                                                           

type UserRole struct {                                      
    Coucou string `gorm:"column:coucou;"`
    UserID string `gorm:"primarykey;column:UserID;"`
    RoleID string `gorm:"primarykey;column:RoleID;"`
}

and generate a migration with :

  func main() {                                                                                                                                    
      stmts, err := gormschema.New("postgres").
         Load(
             &postgresql.UserRole{},
             &postgresql.User{},
             &postgresql.Role{},                                                   
        )                                                                         
      if err != nil {                                
          fmt.Fprintf(os.Stderr, "failed to load gorm schema: %v\n", err)                                
          os.Exit(1)                                
      }                                
      io.WriteString(os.Stdout, stmts)                                

I get:

-- Create "roles" table                                                                                                                            
CREATE TABLE "roles" (
    "id" text NOT NULL,                                
    "description" text NULL,                                                    
    PRIMARY KEY ("id")                                                 
);                                        
-- Create "users" table                                     
CREATE TABLE "users" (                                                         
    "id" text NOT NULL,                                                                          
    "name" text NULL,                                                                 
    PRIMARY KEY ("id")                                                              
);                                
-- Create "user_roles" table                                                   
CREATE TABLE "user_roles" (                                
    "role_id" text NOT NULL,                                         
    "user_id" text NOT NULL,                                     
    PRIMARY KEY ("role_id", "user_id"),                                             
    CONSTRAINT "fk_user_roles_role" FOREIGN KEY ("role_id") REFERENCES "roles" ("id") ON UPDATE CASCADE ON DELETE CASCADE,
    CONSTRAINT "fk_user_roles_user" FOREIGN KEY ("user_id") REFERENCES "users" ("id") ON UPDATE CASCADE ON DELETE CASCADE
);                                                                                                       

Am I missing something? Thank you so much!

[luantranminh]

Hey @jeandeducla, thank you for reaching out. I noticed that you're using Customize JoinTable but haven't set it up yet. It's a required step for GORM to recognize your JoinTable. We have an adapter for configuring it with the WithJoinTable option. Here is an example: https://github.com/ariga/atlas-provider-gorm?tab=readme-ov-file#frequently-asked-questions

[jeandeducla]

Thanks luantranminh! It's exactly what I was looking for. Sorry I did no see that part of the documentation.

[luantranminh]

@jeandeducla Hey, we've just released a new version of the provider that automatically sets up the Customize JoinTable. Your old code still works, we maintain backward compatibility 🎉. But the new one is more handy and seamlessly does all the jobs. If you feel interested, you can check it out with v0.5.0.

There is no need for the WithJoinTable option anymore.

func main() {                                                                                                                                    
    stmts, err := gormschema.New("postgres").
       Load(
           &postgresql.UserRole{},
           &postgresql.User{},
           &postgresql.Role{},                                                   
      )                                                                         
    if err != nil {                                
        fmt.Fprintf(os.Stderr, "failed to load gorm schema: %v\n", err)                                
        os.Exit(1)                                
    }                                
    io.WriteString(os.Stdout, stmts)   
}