Pg: Foreign key is not created when I ignore inverse side relation.

[kopaygorodsky]

type Quest struct {
    UID           string    `sql:",pk"`
    CreatedAt     time.Time
    DefaultLocale string
    Translations []QuestTranslation
}
type QuestTranslation struct {
    UID         string    `sql:",pk"`
    Locale      string
    QuestId     string
    Name        string
    Description string
}

go-pg creates quest_translation table with quest_id field, but it's not a foreign key(FKConstraints is enabled). The only way to get it working is to add Quest *Quest field to QuestTranslation struct. ->

type QuestTranslation struct {
    UID         string    `sql:",pk"`
    Locale      string
    QuestId     string
        Quest        *Quest
    Name        string
    Description string
}

But I don't want any inverse relations, QuestTranslation shouldn't know about Quest.
Also, I'm not sure how this works here
https://github.com/go-pg/pg/blob/c6aec121b684da501174e329b495d55adf7232c4/example_model_test.go#L667

[vmihailenco]

How do you imagine go-pg should know that QuestId is a foreign key?

[kopaygorodsky]

@vmihailenco tag with a property name? I saw some usage of pg:fk:NAME, but it didn't work.
Also, it would be a good idea to have unidirectional relations.

[maxlandon]

Hello,
I would like to know if the solution to this problem is hard to solve ?
Anyone has had issues with this in large code bases ?
Thanks a lot for the answers !

[maxlandon]

Okay actually it seems to work with this simple trick:
(Maybe I'm missing something, or I have not yet faced the full array of consequences, but anyway...)

type Workspace struct {
        ID int `sql:"workspace_id,pk"`
}

type Host struct {
        ID            int `sql:"host_id,pk"`
        WorkspaceID   int `sql:"workspace_id,notnull,on_delete:CASCADE"`
}

When using these struct tags, and if you check your postgres table, you will see in the 'hosts' table that a foreign key has been created correctly, referencing the good 'workspaces' table.
Also, when deleting the appropriate workspace, it will effectively delete its associated hosts.

So it appears you don't need to embed a struct like you did.

PS: don't forget to use FKConstraint: true as options when using pgdb.CreateTable() !

[kopaygorodsky]

@maxlandon I'll check it on my side if it works, but I gave up using go-pg :)

[maxlandon]

No actually I was wrong, I'm not even able to read my code correctly ! I needed to embed a
*Workspace struct in my Host struct.
Even if I don't use it later.
So the problem is still here !

[StarpTech]

@maxlandon no shame I run into the same condition :smile: it would be great to implement a debug message or at least improve the documentation.

[frederikhors]

@vmihailenco can you please answer here, help us and close this issue? :smile:

I'm in the same situation: i'm using this: https://pg.uptrace.dev/orm/has-many-relation/ to create a one-to-many relation but I don't know how to create a FK (I'm already using FKConstraints: true).

This is the code:

type Profile struct {
    Id     int
    Lang   string
    Active bool
    UserId int
}

// User has many profiles.
type User struct {
    Id       int
    Name     string
    Profiles []*Profile `pg:"rel:has-many"`
}

In this case I expect FK user.id on profiles.user_id column.

Am I wrong?

How to do?

Thanks.