AutoMigrate Does Not Update Existing Check Constraint

[YidiDev]

GORM Playground Link

go-gorm/playground#760

Description

Summary:

When modifying a check constraint on a model field and running AutoMigrate, GORM does not update the existing check constraint in the database if a constraint with the same name already exists. This issue is observed with SQLite and may affect other database dialects as well.

---

Steps to Reproduce:

1. Set up the initial model with a check constraint and run AutoMigrate:

   package main
   
   import (
       "gorm.io/driver/sqlite"
       "gorm.io/gorm"
       "log"
   )
   
   type User struct {
       ID   uint
       Name string `gorm:"check:name_checker,name <> ''"`
   }
   
   func main() {
       db, err := gorm.Open(sqlite.Open("test.db"), &gorm.Config{})
       if err != nil {
           log.Fatalf("failed to connect to database: %v", err)
       }
   
       if err = db.AutoMigrate(&User{}); err != nil {
           log.Fatalf("failed to migrate: %v", err)
       }
   }

2. Modify the check constraint in the model and run AutoMigrate again:

   package main
   
   import (
       "gorm.io/driver/sqlite"
       "gorm.io/gorm"
       "log"
   )
   
   type User struct {
       ID   uint
       Name string `gorm:"check:name_checker,length(name) > 3"`
   }
   
   func main() {
       db, err := gorm.Open(sqlite.Open("test.db"), &gorm.Config{})
       if err != nil {
           log.Fatalf("failed to connect to database: %v", err)
       }
   
       if err = db.AutoMigrate(&User{}); err != nil {
           log.Fatalf("failed to migrate after modification: %v", err)
       }
   }

3. Attempt to insert data that violates the new constraint but satisfies the old one:

   package main
   
   import (
       "fmt"
       "gorm.io/driver/sqlite"
       "gorm.io/gorm"
       "log"
   )
   
   type User struct {
       ID   uint
       Name string
   }
   
   func main() {
       db, err := gorm.Open(sqlite.Open("test.db"), &gorm.Config{})
       if err != nil {
           log.Fatalf("failed to connect to database: %v", err)
       }
   
       user := User{Name: "Hi"} // Length is 2, should violate the new constraint
       err = db.Create(&user).Error
       if err != nil {
           fmt.Printf("Expected error when inserting invalid data: %v\n", err)
       } else {
           fmt.Println("Unexpectedly inserted invalid data that should violate the new constraint")
       }
   
       user = User{Name: "Hello"} // Length is 5, should pass the new constraint
       err = db.Create(&user).Error
       if err != nil {
           fmt.Printf("Error when inserting valid data: %v\n", err)
       } else {
           fmt.Println("Successfully inserted valid data")
       }
   }

4. Observe the Output:

   Unexpectedly inserted invalid data that should violate the new constraint
   Successfully inserted valid data
   

   This indicates that the check constraint was not updated in the database after modifying the model and running AutoMigrate again.

---

Expected Behavior:

• After modifying the check constraint and running AutoMigrate, the existing constraint in the database should be updated to reflect the changes in the model.
• Inserting data that violates the new constraint should result in an error.

Actual Behavior:

• The existing check constraint remains unchanged in the database, and the new constraint definition is not applied.
• Data that violates the new constraint but satisfies the old one can be inserted without errors.

---

Possible Cause:

• The AutoMigrate function checks for the existence of constraints by name and skips creation if the constraint exists, without verifying if the constraint definition has changed.

---

Environment:

• GORM version: v1.25.12
• Go version: go 1.23.0
• SQLite version: Using gorm.io/driver/sqlite but I was able to reproduce the same problem in postgres (I didn't try with other dialects but imagine the same issue exists)
• OS: Windows 11

---

Additional Context:

• This issue may also affect other database dialects.
• Updating constraints is crucial for maintaining data integrity and ensuring that the database schema stays in sync with the application's data validation logic.

---

Workaround:

As a temporary workaround, manually drop the existing constraint before running AutoMigrate:

db.Migrator().DropConstraint(&User{}, "name_checker")
db.AutoMigrate(&User{})

This forces the constraint to be recreated with the updated definition.

---

Possible Solution:

• Modify the AutoMigrate logic to drop existing constraints with the same name before creating new ones.
• Alternatively, compare the existing constraint definition with the new one and update it if they differ.

Note: I apologies for the duplicate issue submissions. I was figuring out how to do the Playground Pull Request.

[Mghasemzadeh]

Hi,
I am working on it.