CODEWITHSUNDEEP
postgresqlgodata-structuresgo-gorm
Robert Pelican
Robert Pelican

Reputation: 91

Automigrate in GORM database adds unwanted fields to SQL table

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.

Upvotes: 0

Views: 3156

Answers (2)

Robert Pelican
Robert Pelican

Reputation: 91

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.

Upvotes: 0

Akshay Deep Giri
Akshay Deep Giri

Reputation: 3280

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.

Upvotes: 5

Related Questions