Search code examples
databasegogo-gorm

Gorm: Cannot add or update a child row - a foreign key constraint fails on self referencing


I have a struct which looks like this:

type Category struct {
    Code           *int      `gorm:"unique;primaryKey;"`
    ParentCategory *Category `gorm:"foreignKey:Code"`
}

A Category itself can have a ParentCategory which is also from type Category. So it references to itself. If its the first Category it has NO ParentCategory.

I have an array whith 4 Categories, as said, the first one doesnt have ab ParentCategory.

When saving these Categories one after one (Beginning with the first which has NO ParentCategory, I get these errors (Just print here the first two):

Error 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`ps_product_service`.`categories`, CONSTRAINT `fk_categories_parent_category` FOREIGN KEY (`code`) REFERENCES `categories` (`code`))
[20.890ms] [rows:0] INSERT INTO `categories` (`code`) VALUES (0) RETURNING `code`
Error when creating category: Error 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`ps_product_service`.`categories`, CONSTRAINT `fk_categories_parent_category` FOREIGN KEY (`code`) REFERENCES `categories` (`code`))&{Code:0x140003c6a00 ParentCategory:<nil>}
2023/06/19 21:31:44 Error 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`ps_product_service`.`categories`, CONSTRAINT `fk_categories_parent_category` FOREIGN KEY (`code`) REFERENCES `categories` (`code`))
[7.689ms] [rows:0] INSERT INTO `categories` (`code`) VALUES (99) RETURNING `code`
Error when creating category: Error 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`ps_product_service`.`categories`, CONSTRAINT `fk_categories_parent_category` FOREIGN KEY (`code`) REFERENCES `categories` (`code`))&{Code:0x140003c6a20 ParentCategory:<nil>}

I really cant figure out what I have to do here. Could someone help me with this please?


Solution

  • I should have managed your requirements with the following code:

    package main
    
    import (
        "fmt"
    
        "github.com/samber/lo"
        "gorm.io/driver/postgres"
        "gorm.io/gorm"
    )
    
    type category struct {
        Code             uint `gorm:"unique;primaryKey;"`
        ParentCategoryId *uint
        ParentCategory   *category `gorm:"foreignKey:ParentCategoryId"`
    }
    
    func main() {
        dsn := "host=localhost port=54322 user=postgres password=postgres dbname=postgres sslmode=disable"
        db, err := gorm.Open(postgres.Open(dsn))
        if err != nil {
            panic(err)
        }
        db.AutoMigrate(&category{})
    
        // load dummy data
        db.Create(&category{
            Code:             1,
            ParentCategoryId: nil,
        })
        db.Create(&category{
            Code:             2,
            ParentCategoryId: lo.ToPtr[uint](1),
        })
        db.Create(&category{
            Code:             3,
            ParentCategoryId: lo.ToPtr[uint](2),
        })
        db.Create(&category{
            Code:             4,
            ParentCategoryId: lo.ToPtr[uint](1),
        })
    
        // reading logic
        var categories []category
        if err := db.Model(&category{}).Find(&categories).Error; err != nil {
            panic(err)
        }
        for _, v := range categories {
            if v.ParentCategoryId == nil {
                fmt.Printf("id: %v\tparentCategoryId: <nil>\n", v.Code)
                continue
            }
            fmt.Printf("id: %v\tparentCategoryId: %v\n", v.Code, *v.ParentCategoryId)
        }
    }
    

    If I got well your needs, you need to have a table that references itself. That's why I defined the category struct in this way. Each category has its own ParentCategoryId unless the category has no parent. If you try to execute the previous code, you should get something like:

    id: 1   parentCategoryId: <nil>
    id: 2   parentCategoryId: 1
    id: 3   parentCategoryId: 2
    id: 4   parentCategoryId: 1
    

    Maybe I didn't get something from your question, if that's the case just let me know and I'll update my answer, thanks!