Question 115043 in PostgreSQL Question posted in
The official documentation can be found here.

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

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

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    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

  2. 0

    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:

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search