GORM Playground Link

https://github.com/go-gorm/playground/pull/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:

```go 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)
   }

} ```

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

```go 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)
   }

} ```

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

```go 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")
   }

} ```

  1. 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.

Comment From: Mghasemzadeh

Hi, I am working on it.

Comment From: Mghasemzadeh

Hi, I’ve been working on this issue and I believe I’ve identified the root cause. I see two possible ways to handle it. The simpler approach is to drop the constraint regardless of whether it has changed and recreate it. This method is straightforward and requires minimal changes, but it may drop and recreate constraints unnecessarily.

The alternative approach is to check if the existing constraint is different from the new only drop it if a change is detected. This method is more efficient but requires additional logic and a new function to compare constraints. I’m leaning toward the simpler approach due to its simplicity, but I’m happy to work on the optimized approach if preferred.

I would appreciate the maintainers' guidance on which approach would be most suitable. @jinzhu