dongshi2588
2019-08-26 21:48
浏览 278

GORM数据库中的自动迁移将不必要的字段添加到SQL表

When I create my tables in the gorm database, it adds columns to the table that I don't want. I'm not sure how it's adding these extra fields. This causes me to run into an error that says, "pq: null value in column "user_id" violates not-null constraint". "user_id" is the unwanted column that gets added. I'm using gorm and postgreSQL.

I have a many to many relationship for my two tables. My first table is created properly and my second table, stores, is created with the provided fields plus two unwanted fields: "user_id" and "stores_id". I've tried removing the many to many relationship to see if that was the issue, I've tried dropping the tables and recreating them with different fields. Regardless, I have not been able to get rid of the two extra columns.

The first (working) table:

type User struct {
    gorm.Model
    ID int `json:"u_id"`
    Name string `json:"u_name"`
    Stores []Store `gorm:"many2many:stores;" json:"stores"`
}

When I execute '\d users', I get the following columns: id, created_at, updated_at, deleted_at, name.

The second (problematic) table:

type Stores struct {
    gorm.Model
    ID int `json:"s_id"`
    NUM int `gorm:"unique" json:"s_num"`
    Users []User `gorm:"many2many:user" json:"users"`
}

When I execute '\d' stores, I get the following columns: user_id, vehicle_id, id, created_at, updated_at, deleted_at, num.

I'm executing the creation of these tables through the following code:

db.AutoMigrate(&User{})
db.AutoMigrate(&Store{})

On another note, if I add gorm:"primary_key";auto_increment" to my ID values in my structs, I get the error "pq: column "user_id" appears twice in primary key constraint". I was able to get around this by removing the primary_key and auto_increment attributes, running AutoMigrate() and then adding it back in and running AutoMigrate() again - this was totally fine and working.

I've also tried manually inserting a user_id and store_id. This works fine, except that I'd have to generate new ones every time because they require uniqueness. I understand that the error "pq: null value in column "user_id" violates not-null constraint" is caused by the fact that I'm not providing a user_id or store_id when I'm creating my store. I'm simply confused why a user_id and store_id column is being generated at all, and I'm hoping I can fix that.

  • 写回答
  • 关注问题
  • 收藏
  • 邀请回答

2条回答 默认 最新

  • doujia1163 2019-08-27 16:26
    已采纳

    Fixed the duplicate ID errors by removing gorm.Model, as @(Akshaydeep Girl) pointed out what having gorm.Model entails. As for the random 'user_id' and 'store_id' that kept automatically being added, they were being added because of the many2many gorm relationship. I was able to remove those by switching the order of migration.

    func DBMigrate(db *gorm.DB) *gorm.DB {
        db.AutoMigrate(&Store{})
        db.AutoMigrate(&User{})
        return db
    }
    

    When I dropped both tables and re-compiled/ran my project with the new order of migration, the stores table was created without the random 'user_id' and 'store_id', and the users table didn't have those either.

    打赏 评论
  • douli0531 2019-08-27 02:59

    This is what gorm.Model looks like

    type Model struct {
      ID        uint `gorm:"primary_key"`
      CreatedAt time.Time
      UpdatedAt time.Time
      DeletedAt *time.Time
    }
    

    When we call gorm.Model inside a struct, it means we are add default fields of gorm.Model in our current struct.

    type Stores struct {
        gorm.Model
    ....
    

    so your user model will look something like

    ype User struct {
        ID        uint `gorm:"primary_key"`
        CreatedAt time.Time
        UpdatedAt time.Time
        DeletedAt *time.Time
        ID int `json:"u_id"`
        Name string `json:"u_name"`
        Stores []Store `gorm:"many2many:stores;" json:"stores"`
    }
    

    that error mayne due to duplicate primary_key key. try to rename ID intjson:"u_id"`` to UserID. you need to update Stores too.

    打赏 评论

相关推荐 更多相似问题