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

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

CodePudding user response：

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:

CodePudding user response：

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