Gorm: Unable to add or update child row - foreign key constraint fails on self-reference

# During the development process, php editor Xinyi sometimes encounters the error "Cannot add or update child row - foreign key constraint failed on self-reference" error. This error usually occurs when there are self-references in the database, such as a field in a table that references another field in the table. In this case, if the foreign key constraint is not configured correctly, it will cause an error that the child row cannot be added or updated. Next, we will introduce some methods to solve this problem.

Question content

I have a structure as shown below:

type category struct {
    code           *int      `gorm:"unique;primarykey;"`
    parentcategory *category `gorm:"foreignkey:code"`
}

Category itself can have a parentcategory, which also comes from the category type. So it references itself. If it is the first category, it has no parent category.

I have an array with 4 categories, as mentioned above, the first one does not have ab parentcategory.

When saving these categories one after another (starting with the first one without parentcategory, I get these errors (just print the first two here):

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 don't know what I have to do here. Can anyone help me solve this problem?

Solution

I should use the following code to manage your needs:

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 understand what you need, you need to have a table that references itself. That's why I defined the category structure this way. Each category has its own parentcategoryid unless the category has no parent. If you try to execute the preceding code, you should get something like this:

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 so please tell me and I will update my answer, thank you!

The above is the detailed content of Gorm: Unable to add or update child row - foreign key constraint fails on self-reference. For more information, please follow other related articles on the PHP Chinese website!