Many2Many associations cause AutoMigrate to create joining tables with the wrong data types; then foreign key constraints fail.

[brett--anderson]

GORM Playground Link

I can't add a link because the playground repo hasn't been working since April! See here go-gorm/playground#730

If that gets fixed at some point (and I haven't moved on from Gorm to solve my problem) I'll come back and provide a link to a fork that demonstrates the issue.

Description

Given the above, I don't know if this project's dead, but here's the issue anyway:

modules:

gorm.io/driver/mysql v1.5.2
gorm.io/gen v0.3.25
gorm.io/gorm v1.25.4

The database has the structure of a User table, a many-to-many User_Account table and an Account table.

CREATE TABLE user (
       id INT NOT NULL AUTO_INCREMENT, 
       PRIMARY KEY (id), 
       UNIQUE (id)
)
    
CREATE TABLE account (
       id INT NOT NULL AUTO_INCREMENT, 
       UNIQUE (id)
)
    
CREATE TABLE user_account (
       user_id INT NOT NULL, 
       account_id INT NOT NULL, 
       PRIMARY KEY (user_id, account_id), 
       FOREIGN KEY(account_id) REFERENCES account (id) 
               ON DELETE CASCADE ON UPDATE CASCADE, 
       FOREIGN KEY(user_id) REFERENCES user (id)
)

Using Gorm Gen, I generated the following structs from an existing version of the database, though note I had to manually add the Accounts field to the User struct (annoying that Gorm Gen dosen't do that automatically, also annoying that it doesn't identify unsigned int SQL data types and uses int32 instead of uint, any way, that's for another bug post)

type User struct {
	ID       int32     `gorm:"column:id;primaryKey;autoIncrement:true" json:"id"`
	Name     string    `gorm:"column:name" json:"name"`
	Accounts []Account `gorm:"many2many:user_account"`
}

type Account struct {
	ID   int32  `json:"id" gorm:"primary_key"`
	Name string `json:"name"`
}

When I use Auto Migrate to create these tables in a new database, I get the error:

Error 1215 (HY000): Cannot add foreign key constraint
[11.180ms] [rows:0] CREATE TABLE `user_account` (`user_id` int,`account_id` bigint unsigned,
PRIMARY KEY (`user_id`,`account_id`),CONSTRAINT `fk_user_account_user` 
FOREIGN KEY (`user_id`) REFERENCES `user`(`id`),
CONSTRAINT `fk_user_account_account` 
FOREIGN KEY (`account_id`) REFERENCES `account`(`id`))
Error migrating model &{{0  } []}: Error 1215 (HY000): Cannot add foreign key constraint

Note that when it inferred the user_account table and tried to create it, it set the account_id field as bigint unsigned for some reason?!?! This of course doesn't match the Account table that was created in MySQL by AutoMigrate from the Account struct, as it's ID is clearly int32, so the SQL command fails. Where is it pulling that bigint unsigned data type from?

[github-actions]

The issue has been automatically marked as stale as it missing playground pull request link, which is important to help others understand your issue effectively and make sure the issue hasn't been fixed on latest master, checkout https://github.com/go-gorm/playground for details. it will be closed in 30 days if no further activity occurs. if you are asking question, please use the Question template, most likely your question already answered https://github.com/go-gorm/gorm/issues or described in the document https://gorm.io ✨ Search Before Asking ✨

[github-actions]

The issue has been automatically marked as stale as it missing playground pull request link, which is important to help others understand your issue effectively and make sure the issue hasn't been fixed on latest master, checkout https://github.com/go-gorm/playground for details. it will be closed in 30 days if no further activity occurs. if you are asking question, please use the Question template, most likely your question already answered https://github.com/go-gorm/gorm/issues or described in the document https://gorm.io ✨ Search Before Asking ✨