gorm m2m custom connection table, foreign key constraint setting is invalid

Question

I try to migrate mysql's sql to gorm

sql:

CREATE TABLE IF NOT EXISTS `admin` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    PRIMARY KEY (`id`)
) ENGINE = InnoDB;

CREATE TABLE IF NOT EXISTS `group` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(100) NOT NULL,
    PRIMARY KEY (`id`)
) ENGINE = InnoDB;

CREATE TABLE IF NOT EXISTS `admin_group` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `admin_id` BIGINT UNSIGNED NOT NULL,
    `group_id` BIGINT UNSIGNED NOT NULL,
    `create_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `update_at` DATETIME ON UPDATE CURRENT_TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `admin_id` (`admin_id`),
    KEY `group_id` (`group_id`),
    UNIQUE (`admin_id`, `group_id`),
    FOREIGN KEY (`admin_id`) REFERENCES `admin`(`id`) ON DELETE CASCADE ON UPDATE CASCADE,
    FOREIGN KEY (`group_id`) REFERENCES `group`(`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE = InnoDB;

gorm:

type Admin struct {
    ID     uint64
    Groups *[]Group `gorm:"many2many:AdminGroup;foreignKey:id;joinForeignKey:admin_id;References:id;joinReferences:group_id;constraint:OnUpdate:CASCADE,OnDelete:CASCADE;"`
}

type Group struct {
    ID   uint64
    Name string
}

type AdminGroup struct {
    ID       uint64
    AdminID  uint64     `gorm:"index:,unique,composite:admgro;not null;"`
    GroupID  uint64     `gorm:"index:,unique,composite:admgro;not null;"`
    CreateAt *time.Time `gorm:"autoCreateTime;"`
    UpdateAt *time.Time `gorm:"autoCreateTime;"`
}

    err = db.AutoMigrate(&Admin{}, &Group{}, &AdminGroup{})
    if err != nil {
        panic(err)
    }
    err = db.SetupJoinTable(&Admin{}, "Groups", &AdminGroup{})
    if err != nil {
        panic(err)
    }

But the admin_group table created by gorm has no foreign key constraints

gorm ddl:

CREATE TABLE `admin_group` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `admin_id` bigint unsigned NOT NULL,
  `group_id` bigint unsigned NOT NULL,
  `create_at` datetime DEFAULT NULL,
  `update_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_admin_group_admgro` (`admin_id`,`group_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

I hope to get the same ddl as mysql sql

sql ddl:

CREATE TABLE `admin_group` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `admin_id` bigint unsigned NOT NULL,
  `group_id` bigint unsigned NOT NULL,
  `create_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `update_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `admin_id_2` (`admin_id`,`group_id`),
  KEY `admin_id` (`admin_id`),
  KEY `group_id` (`group_id`),
  CONSTRAINT `admin_group_ibfk_1` FOREIGN KEY (`admin_id`) REFERENCES `admin` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `admin_group_ibfk_2` FOREIGN KEY (`group_id`) REFERENCES `group` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

How do I need to modify my gorm model to correctly generate foreign key constraints?

---

gorm.io/driver/mysql v1.5.1 
gorm.io/gorm v1.25.4 
github.com/go-sql-driver/mysql v1.7.1 
go version go1.21.0 linux/amd64 
mysql:8

Answer 1

I have tried with your mentioned structs.By making two changes

1. Changing Admin{},Group{},AdminGroup{} struct's ID as "primaryKey"

2. Calling AutoMigrate() for every struct separately

code:-

type Admin struct {
    ID     uint64   `gorm:"primaryKey"`
    Groups *[]Group `gorm:"many2many:AdminGroup;foreignKey:id;joinForeignKey:admin_id;References:ID;joinReferences:group_id;constraint:OnUpdate:CASCADE,OnDelete:CASCADE;"`
}

type Group struct {
    ID   uint64 `gorm:"primaryKey"`
    Name string
}

type AdminGroup struct {
    ID       uint64     `gorm:"primaryKey"`
    AdminID  uint64     `gorm:"index:,unique,composite:admgro;not null"`
    GroupID  uint64     `gorm:"index:,unique,composite:admgro;not null"`
    CreateAt *time.Time `gorm:"autoCreateTime;"`
    UpdateAt *time.Time `gorm:"autoCreateTime;"`
}
     err := DB.AutoMigrate(&AdminGroup{})
    if err != nil {
        log.Fatal(err)
    }
    err = DB.AutoMigrate(&Group{})
    if err != nil {
        log.Fatal(err)
    }
    err = DB.AutoMigrate(&Admin{})
    if err != nil {
        log.Fatal(err)
    }
    err = DB.SetupJoinTable(&Admin{}, "Groups", &AdminGroup{})
    if err != nil {
        panic(err)
    }

GORM DLL:-

CREATE TABLE `admin_group` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `admin_id` BIGINT UNSIGNED NOT NULL,
  `group_id` BIGINT UNSIGNED NOT NULL,
  `create_at` DATETIME(3) DEFAULT NULL,
  `update_at` DATETIME(3) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_admin_group_admgro` (`admin_id`,`group_id`),
  KEY `fk_admin_group_group` (`group_id`),
  CONSTRAINT `fk_admin_group_admin` FOREIGN KEY (`admin_id`) REFERENCES `admin` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_admin_group_group` FOREIGN KEY (`group_id`) REFERENCES `group` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci