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

Postgresql – Was not able to set foreign keys in tables with gorm data models

I am trying to set foreign key in the data models using gorm
this is the code that I have written

package main

import (
    "log"

    "database/sql/driver"
    "encoding/json"
    "errors"
    "time"

    "gorm.io/driver/postgres"
    "gorm.io/gorm"
)

type JSONB map[string]interface{}

func (j JSONB) Value() (driver.Value, error) {
    if j == nil {
        return nil, nil
    }
    return json.Marshal(j)
}

func (j *JSONB) Scan(value interface{}) error {
    b, ok := value.([]byte)
    if !ok {
        return errors.New("failed to unmarshal JSONB value")
    }

    var v interface{}
    if err := json.Unmarshal(b, &v); err != nil {
        return err
    }

    *j, ok = v.(map[string]interface{})
    if !ok {
        return errors.New("failed to convert JSONB value")
    }

    return nil
}

type Tag struct {
    Tag_id   int    `gorm:"primary_key;not null"`
    Tag_name string `gorm:"type:varchar(255);not null"`
}

type User_Entity struct {
    Id    string `gorm:"type:varchar(36);not null;unique"`
    Email string `gorm:"type:varchar(255);not null"`
}

type Log_Settings struct {
    Log_Setting_Id   int    `gorm:"primary_key;not null"`
    Log_Setting_Name string `gorm:"type:varchar(20);not null"`
    Creator_Id       string `gorm:"type:varchar(36);not null"`

    User_Entity User_Entity `gorm:"foreignKey:Creator_Id"`
}

type Pass_Fail_Categories struct {
    Category_Id   int    `gorm:"primary_key;unique;not null"`
    Category_Name string `gorm:"type:varchar(15);not null"`
}

type Pass_Fail_Category_Messages struct {
    Category_Id      int    `gorm:"not null"`
    Messages string `gorm:"type:varchar(50);unique;not null"`

    Pass_Fail_Categories Pass_Fail_Categories `gorm:"foreignKey:Category_Id"`
}

type Pass_Fail_Criteria_Feilds struct {
    Criteria_Id      int    `gorm:"primary_key;not null"`
    Category_Id      int    `gorm:"not null"`
    Category_Message string `gorm:"type:varchar(50);not null"`
    Numeric_Operator string `gorm:"type:varchar(2);not null"`
    Criteria_Value   int    `gorm:"not null"`

        Pass_Fail_Categories *Pass_Fail_Categories `gorm:"foreignKey:Category_Id"`
        Pass_Fail_Category_Messages Pass_Fail_Category_Messages `gorm:"foreignKey:Category_Message;references:Messages"`
}

func main() {
    db, err := gorm.Open(postgres.Open("host=localhost user=postgres password=1234567 dbname=tester port=5432 sslmode=disable"), &gorm.Config{})
    if err != nil {
        log.Println("connection failed")
    }

    db.AutoMigrate(&Tag{})
    db.AutoMigrate(&User_Entity{})
    db.AutoMigrate(&Pass_Fail_Categories{})
    db.AutoMigrate(&Pass_Fail_Category_Messages{})
    db.AutoMigrate(&Pass_Fail_Criteria_Feilds{})
}

Here I am then auto migrating these tables but for some cases the foreign keys are not getting reflect and for sometimes it’s giving error

"CREATE TABLE Pass_Fail_Criteria_Feilds
(
  Criteria_Id SERIAL PRIMARY KEY NOT NULL,
  Category_Id INTEGER REFERENCES Pass_Fail_Categories(Category_Id) NOT NULL,
  Category_Message VARCHAR(50) REFERENCES Pass_Fail_Category_Messages(Messages) NOT NULL,
  Numeric_Operator VARCHAR(2) NOT NULL,
  Criteria_Value INTEGER NOT NULL
)"

this is a table structure that I am trying to achieve how can I do that?

Tags:

2

Answers


  1. 0

    Assuming you are connected to the correct database and have the necessary permissions to create tables and establish foreign key relationships, I would recommend first that you handle all errors: that will give you more information on what is going on.

    func main() {
    db, err := gorm.Open(postgres.Open("host=localhost user=postgres password=1234567 dbname=tester port=5432 sslmode=disable"), &gorm.Config{})
    if err != nil {
        log.Fatalf("Connection failed: %v", err)
    }

    // Ensure that referenced tables are created first
    if err := db.AutoMigrate(&Pass_Fail_Categories{}); err != nil {
        log.Fatalf("Failed to auto-migrate Pass_Fail_Categories: %v", err)
    }

    if err := db.AutoMigrate(&Pass_Fail_Category_Messages{}); err != nil {
        log.Fatalf("Failed to auto-migrate Pass_Fail_Category_Messages: %v", err)
    }

    if err := db.AutoMigrate(&Pass_Fail_Criteria_Fields{}); err != nil {
        log.Fatalf("Failed to auto-migrate Pass_Fail_Criteria_Fields: %v", err)
    }

    // ... other AutoMigrate calls ...
}

    Make sure that the column you are referencing as a foreign key exists in the other table and that its data type matches the foreign key column.
    For example, Pass_Fail_Categories should have a column Category_Id before you can use it as a foreign key in another table.

    It is important that the tables referenced by foreign keys are created before the tables that reference them. In your main function, make sure you are calling AutoMigrate in the correct order.

    Finally, In GORM, you have to ensure that you follow the GORM conventions for naming foreign keys, or explicitly specify the foreign key constraints using the struct tags (which you did with gorm:"foreignKey:Creator_Id").

    Login or Signup to reply.
  2. 0

    One thing to keep in mind is that GORM requires you to specify the foreign key field explicitly in your model struct.

    In your Pass_Fail_Criteria_Feilds struct, you have specified the Category_Id field as the foreign key for the Pass_Fail_Categories table, but you have not specified the foreign key field for the Pass_Fail_Category_Messages table. To do this, you can add a gorm:"foreignKey:Category_Message" tag to the Pass_Fail_Category_Messages field in your Pass_Fail_Criteria_Feilds struct.

    Additionally, you may want to try using the references tag to specify the referenced column in the foreign table. For example, you can modify the Category_Id field in your Pass_Fail_Criteria_Feilds struct to include the references tag like this: Category_Id int references Pass_Fail_Categories(Category_Id).

    Finally, make sure that the foreign key fields in your model structs match the data types of the primary key fields in the referenced tables. If the data types do not match, you may encounter errors when trying to set up the foreign key constraints.

    I hope this helps!

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

Back To Top
Search