GORM Error on insert or update on table violates foreign key constraint

user_8685016 0
Viewed 101

I have the following set of GORM models, with 2 orders of one-to-many relations:

type Order struct {
    ID       string  `gorm:"column:id"`
    ClientID string  `gorm:"primaryKey;column:client_id"`
    Name     string  `gorm:"column:name"`
    Albums   []Album `gorm:"foreignKey:RequestClientID"`
}

type Album struct {
    AlbumID         string    `gorm:"primaryKey;column:album_id"`
    RequestClientID string    `gorm:"foreignKey:ClientID;column:request_client_id"`
    Pictures        []Picture `gorm:"foreignKey:AlbumID"`
}

type Picture struct {
    PictureID   string `gorm:"primaryKey;column:picture_id"`
    AlbumID     string `gorm:"foreignKey:AlbumID;column:album_id"`
    Description string `gorm:"column:description"`
}

When I attempt to insert data as follows, I get the error pq: insert or update on table "albums" violates foreign key constraint "fk_orders_albums".

test := Order{
    ID:       "abc",
    ClientID: "client1",
    Name:     "Roy",
    Albums: []Album{
        {
            AlbumID: "al_1",
            Pictures: []Picture{
                {
                    PictureID:   "pic_1",
                    Description: "test pic",
                },
            },
        },
    },
}

gormDB.Save(&test)

I followed the solution on this similar question, but can't seem to get it to work: Golang: Gorm Error on insert or update on table violates foreign key contraint

go-gorm go postgresql
2 Answers

Based on your entity model, Your schema would be like this:

  • Orders table is parent table, didn't depend to any table
  • Albums table has foreign key request_client_id which refer to orders table column client_id
  • Picture table has foreign key album_id which is refer to albums table column album_id

Based on my exploration in gorm documentation here, object in struct will be examine as first association. so, your struct will execute insert to albums first which it violate foreign key schema (expect: insert to orders table should be executed before albums).

But if you want to force using your schema, you can use gorm Association feature. Here is the idea of using Association:

Expected Query in high level:

  • Insert to orders table
  • Insert to albums table
  • Insert to pictures table

Association Ideas:

  • Let Albums field value in Orders struct empty
  • Append Association Albums to Orders model
  • Since there have another association in Albums, use configuration FullSaveAssociation

Here is the following code:

picture := []Picture{
    {
        PictureID:   "pic_1",
        Description: "test pic",
    },
}
albums := []Album{
    {
        ID:              "al_1",
        Pictures:        picture,
        RequestClientID: "",
    },
}
orders := Order{
    ID:       "abc",
    ClientID: "client1",
    Name:     "Roy",
}
if err := gormDB.Save(orders).Error; err != nil {
    return
}
if err := gormDB.Session(&gorm.Session{FullSaveAssociations: true}).Model(&orders).Association("Albums").Append(albums); err != nil {
    return
}

Full code could be found here:

user_52957360

Came across the solution I was looking for while looking into the great answer @giyuu provided. Since I had not saved the item before, I needed to use GORM's Create method:

err = gormDB.Create(&test).Error

Then, when I want to perform an update on any of these values, I use the Save method, with FullSaveAssociations enabled:

err = gormDB.Session(&gorm.Session{FullSaveAssociations: true}).Save(&test).Error
user_86850160
Related