FOREIGN KEY constraint failed (1811) on AutoMigrate when set `foreign_keys` ON for SQLite

[KiddoV]

GORM Playground Link

N/A

Description

My model:

type User struct {
    //Main columns
    UserId        uint   `gorm:"primaryKey;autoIncrement" json:"userId"`
    UserFirstName string `gorm:"not null;default:NULL" json:"userFirstName"`
    UserLastName  string `gorm:"not null;default:NULL" json:"userLastName"`
    UserUsername  string `gorm:"not null;default:NULL;unique;<-:create" json:"userUsername,omitempty"`
    UserPassword  string `gorm:"not null;default:NULL" json:"userPassword,omitempty"`
    UserRole      URole  `gorm:"not null;default:OPERATOR" json:"userRole,omitempty"`
    UserAvatar    []byte `gorm:"default:NULL;size:2097152" json:"userAvatar"` //Avatar only allow maximum size of 2MB=2097152Bytes
    //Extra fields
    AuthLevel uint `gorm:"-:all" json:"authLevel,omitempty"`
}

type XDFirmware struct {
    FWId        string  `gorm:"primaryKey;not null;unique" json:"fwId"`
    Version     string  `gorm:"not null;uniqueIndex:idx_fw_vers" json:"version"`
    MbedVersion string  `gorm:"not null;uniqueIndex:idx_fw_vers" json:"mbedVersion"` //Version string when do `ati` command
    Freq        string  `gorm:"not null;uniqueIndex:idx_fw_vers" json:"freq"`
    TargetXdot  string  `gorm:"not null;uniqueIndex:idx_fw_vers" json:"targetXdot"` //Based on `controller.XDType`
    Checksum    string  `gorm:"not null;unique" json:"checksum"`
    Path        *string `gorm:"default:NULL;check:COALESCE(path, file_content) IS NOT NULL" json:"path"` //Path where the firmware located
    File        struct {
        FileContent *[]byte `gorm:"default:NULL;check:COALESCE(path, file_content) IS NOT NULL" json:"fileContent"`                     //Uploaded file. The firmware stored as bytes
        FileName    *string `gorm:"check:file_content IS NULL OR (file_content IS NOT NULL AND file_name IS NOT NULL)" json:"fileName"` //The name of the uploaded file
        MimeType    *string `gorm:"check:file_content IS NULL OR (file_content IS NOT NULL AND mime_type IS NOT NULL)" json:"mimeType"` //Mime type for the uploaded file
    } `gorm:"embedded" json:"file"`
    //CRUD
    CreateAt time.Time `gorm:"autoCreateTime:RFC3339" json:"createAt"`
    UpdateAt time.Time `gorm:"autoUpdateTime:RFC3339" json:"updateAt"`
    //Belongs To
    CreatedBy     *uint `gorm:"" json:"createdBy"`
    CreatedByInfo User  `gorm:"foreignKey:CreatedBy;references:UserId;constraint:OnUpdate:CASCADE,OnDelete:SET NULL;" json:"createdByInfo"`
    //Extra fields
    PathFileChecksum string `gorm:"-:all" json:"pathFileChecksum,omitempty"`
    UpFileChecksum   string `gorm:"-:all" json:"upFileChecksum,omitempty"`
}

type Item struct {
    ItemId     uint   `gorm:"primaryKey;autoIncrement" json:"itemId"`
    ItemNumber string `gorm:"not null;uniqueIndex:idx_item" json:"itemNumber"`
    XdotType   string `gorm:"not null;uniqueIndex:idx_item" json:"xdotType"`
    //CRUD
    CreateAt time.Time `gorm:"autoCreateTime:RFC3339" json:"createAt"`
    UpdateAt time.Time `gorm:"autoUpdateTime:RFC3339" json:"updateAt"`
    //Belongs To
    CreatedBy         *uint      `gorm:"" json:"createdBy"`
    CreatedByInfo     User       `gorm:"foreignKey:CreatedBy;references:UserId;constraint:OnUpdate:CASCADE,OnDelete:SET NULL;" json:"createdByInfo"`
    TestFirmware      *string    `gorm:"" json:"testFirmware"`
    TestFirmwareInfo  XDFirmware `gorm:"foreignKey:TestFirmware;references:FWId;constraint:OnUpdate:CASCADE,OnDelete:RESTRICT;" json:"testFirmwareInfo"`
    WriteFirmware     *string    `gorm:"" json:"writeFirmware"`
    WriteFirmwareInfo XDFirmware `gorm:"foreignKey:WriteFirmware;references:FWId;constraint:OnUpdate:CASCADE,OnDelete:RESTRICT;" json:"writeFirmwareInfo"`
}

I used "github.com/glebarez/sqlite" for SQlite driver

ALL_MODELS = []any{&User{}, &XDFirmware{}, &Item{}}

dsn := dbPath + "?_pragma=foreign_keys(1)"
if sDB, err := gorm.Open(sqlite.Open(dsn), &gorm.Config{SkipDefaultTransaction: true, PrepareStmt: true}); err != nil {
    return fmt.Errorf("failed to connect to server database, %v", err)
} else {
    if err := sDB.AutoMigrate(ALL_MODELS...); err != nil {
        return fmt.Errorf("failed auto migration for server database, %v", err)
    }
}

The problem occurs when referencing Item.TestFirmware or Item.WriteFirmware with the parent table XDFirmware.FWId. According to the GORM documentation, DropTable should "ignore or delete foreign key constraints when dropping": https://gorm.io/docs/migration.html#Tables

I encounter the foreign key constraint failure when running AutoMigrate.

2024/05/23 07:41:19 C:/Users/.../go/pkg/mod/github.com/glebarez/sqlite@v1.10.0/migrator.go:399 constraint failed: FOREIGN KEY constraint failed (1811)
[1.560ms] [rows:0] DROP TABLE `xd_firmwares`

I know that I can use DisableForeignKeyConstraintWhenMigrating: true to bypasses the error, but it compromises the logic as it allows the deletion of parent rows, which should be restricted due to OnDelete:RESTRICT.

Not sure if this is expected behavior or a bug. Thanks,

[github-actions[bot]]

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[bot]]

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 ✨

[oPOCCOMAXAo]

For migrations you could do:

• in DSN set next param: _foreign_keys=false
• in gorm config set DisableForeignKeyConstraintWhenMigrating: false
• run Automigrate

After this your db's schema will be with new foreign keys

For work you should set param _foreign_keys=true in DSN and all will work fine