How to make foreign key using gorm

Question

I have those two models:

User model:

type User struct {
    DBBase
    Email    string `gorm:"column:email" json:"email"`
    Password string `gorm:"column:password" json:"-"`
}

func (User) TableName() string {
    return "t_user"
}

User info model:

type UserInfo struct {
    User      User   `gorm:"foreignkey:u_id;association_foreignkey:id"`
    UID       uint   `gorm:"column:u_id" json:"-"`
    FirstName string `gorm:"column:first_name" json:"first_name"`
    LastName  string `gorm:"column:last_name" json:"last_name"`
    Phone     string `gorm:"column:phone" json:"phone"`
    Address   string `gorm:"column:address" json:"address"`
}

func (UserInfo) TableName() string {
    return "t_user_info"
}

and I want to make UID related to the id of the user table.

this is the function that creates the user:

func (dao *AuthDAO) Register(rs app.RequestScope, user *models.User, userInfo *models.UserInfo) (userErr error, userInfoErr error) {
    createUser := rs.Db().Create(&user)
    userInfo.UID = user.ID
    createUserInfo := rs.Db().Create(&userInfo)

    return createUser.Error, createUserInfo.Error
}

I did try what gorm wrote on the documentation, but without success: http://doc.gorm.io/associations.html

Jeffrey

What did you try? – Henrion

@Devon the "Belongs To" section – Jeffrey

Yeah... what in that section? You didn't include any relationship in your above code. – Henrion

@Devon, sorry, you right. I edited the code. thanks. – Jeffrey

The foreignkey should be UID, the association foreign key shouldn't be needed if it's id. – Henrion

@Devon this is not working also – Jeffrey

So you have gorm:"foreignkey:UID"? How are you trying to retrieve the relationship? Any errors? – Henrion

@Devon edited the question with the creation function. I don't have any errors. the user created but UID isn't a foreign key (i can change it to any id i wants without any error) – Jeffrey

So you're concerned about the missing index/fk in the schema, not about the ability to pull the related model? Are you migrating with gorm? You need to consult doc.gorm.io/database.html#migration – Henrion

@Devon yes, am do a migration. – Jeffrey

you can try db.Exec("....") for create foreign key – Ponce

Answer 1

Note!

from gorm 2.0 this is no longer necessary, read more here: gorm.io/docs/belongs_to.html#FOREIGN-KEY-Constraints

The solution is to add this line when migrating the database:

db.Model(&models.UserInfo{}).AddForeignKey("u_id", "t_user(id)", "RESTRICT", "RESTRICT")

migration (gorm documentation)

Jeffrey

Thanks for your response ! – Apotheosize

As of gorm 2.0 this is no longer necessary. Follow gorm.io/docs/belongs_to.html#FOREIGN-KEY-Constraints – Wentzel

@Wentzel what if I can't add gorm tags to fields as my models are auto-generated from graphql schema using gqlgen? I can no longer add relations via code? – Grappling

Answer 2

I found the following code correctly created the foreign key without having to do any custom migration code; just the usual AutoMigrate.

type Account struct {
    ID uint `gorm:"primary_key"`
}

type Transaction struct {
    ID        uint `gorm:"primary_key"`
    AccountID uint
    Account   Account `gorm:"constraint:OnUpdate:CASCADE,OnDelete:CASCADE;"`
}

I am using "Gorm 2.0" which is a dependency of gorm.io/gorm v1.23.3.

Wentzel

Answer 3

For recent release of Gorm i.e. 1.21.xIf you have a one to one mapping of fields and not have parent to child mapping, here is what you will do

type BaseConfig struct {
    ID         uuid.UUID       `gorm:"primary_key" json:"id"`
}

type Ledger struct { 
    BaseConfigId        uuid.UUID       `json:"base_config_id"`
    BaseConfig BaseConfig               `gorm:"column:base_config_id;constraint:OnUpdate:CASCADE,OnDelete:SET NULL;"`

}

In Migration script you will need to follow as per docs

WriteClient.Migrator().CreateConstraint(&models.BaseConfig{}, "Ledgers")
WriteClient.Migrator().CreateConstraint(&models.BaseConfig{}, "fk_base_configs_id")

I feel the tripping point is the missing documentation for how to associate. Some juggling around could be there to figure it out, so hope my answer saves some time.

Servia

Answer 4

In our case we needed full flexibility in terms of column/field names, as we were migrating an existing database originally made by SQLAlchemy. Not using the names that Gorm expects results in foreign keys not being made.

Using Gorm 2.0 which for some reason still has v1 tags (gorm.io/gorm v1.25.1), the trick is to use foreignkey (must point to the local field) and references (most point to the remote field in the foreign table) annotations correctly for totally arbitrary names:

type Other struct {
    OtherId int32 `gorm:"primarykey; column:custom_col_other_id"`
}

type Main struct {
    MainId    int32 `gorm:"primarykey; column:custom_col_main_id"`
    FkOtherId int32 `gorm:"column:custom_col_fk_other_id"`
    OtherRef  Other `gorm:"foreignkey:FkOtherId;references:OtherId"`
}

...

dbClient.AutoMigrate(&Other{})
dbClient.AutoMigrate(&Main{})

This results in the correct foreign key relation:

                                        Table "public.others"
       Column        |  Type   | Collation | Nullable |                       Default                       
---------------------+---------+-----------+----------+-----------------------------------------------------
 custom_col_other_id | integer |           | not null | nextval('others_custom_col_other_id_seq'::regclass)
Indexes:
    "others_pkey" PRIMARY KEY, btree (custom_col_other_id)
Referenced by:
    TABLE "mains" CONSTRAINT "fk_mains_other_ref" FOREIGN KEY (custom_col_fk_other_id) REFERENCES others(custom_col_other_id)"

                                            Table "public.mains"
         Column         |  Type   | Collation | Nullable |                      Default                      
------------------------+---------+-----------+----------+---------------------------------------------------
 custom_col_main_id     | integer |           | not null | nextval('mains_custom_col_main_id_seq'::regclass)
 custom_col_fk_other_id | integer |           |          | 
Indexes:
    "mains_pkey" PRIMARY KEY, btree (custom_col_main_id)
Foreign-key constraints:
    "fk_mains_other_ref" FOREIGN KEY (custom_col_fk_other_id) REFERENCES others(custom_col_other_id)

Aweinspiring

Answer 5

Read about the belongs to relationship in https://gorm.io/docs/belongs_to.html Also, there's a good example here: https://medium.com/@the.hasham.ali/how-to-use-uuid-key-type-with-gorm-cc00d4ec7100

// User is the model for the user table.
type User struct {
 Base
 SomeFlag bool    `gorm:"column:some_flag;not null;default:true"`
 Profile  Profile
}// Profile is the model for the profile table.
type Profile struct {
 Base
 Name   string    `gorm:"column:name;size:128;not null;"`
 UserID uuid.UUID `gorm:"type:uuid;column:user_foreign_key;not null;"`
}

Laellaertes

Answer 6

We can add foreign key constraints in the latest version using CreateConstraint.

Example: Suppose we have two entity

type User struct {
  gorm.Model
  CreditCards []CreditCard
}

type CreditCard struct {
  gorm.Model
  Number string
  UserID uint
}

Now create database foreign key for user & credit_cards

db.Migrator().CreateConstraint(&User{}, "CreditCards")
db.Migrator().CreateConstraint(&User{}, "fk_users_credit_cards")

which translates to the following SQL code for Postgres:

ALTER TABLE `credit_cards` ADD CONSTRAINT `fk_users_credit_cards` FOREIGN KEY (`user_id`) REFERENCES `users`(`id`)

Referrence:

• Gorm official doc for Constraint Creation
• Changes in the latest version

Regional