Issue with composite foreign key generation

[taiyangc]

Hello, I have two tables with one composite foreign key referring to another

The simplified pg schema is

                      Table "public.accounts"
    Column     |     Type      | Collation | Nullable |   Default
---------------+---------------+-----------+----------+-------------
 address       | character(34) |           | not null |
 confirmed     | boolean       |           | not null | false
Indexes:
    "accounts_pkey" PRIMARY KEY, btree (address, confirmed)
Referenced by:
    TABLE "assets" CONSTRAINT "assets_owner_address_fkey" FOREIGN KEY (owner_address, confirmed) REFERENCES accounts(address, confirmed)

                            Table "public.assets"
    Column     |            Type             | Collation | Nullable | Default
---------------+-----------------------------+-----------+----------+---------
 id            | character(7)                |           | not null |
 confirmed     | boolean                     |           | not null | false
 owner_address | character(34)               |           | not null |
Indexes:
    "assets_pkey" PRIMARY KEY, btree (id, confirmed)
Foreign-key constraints:
    "assets_owner_address_fkey" FOREIGN KEY (owner_address, confirmed) REFERENCES accounts(address, confirmed)

When using genna to generate separated models, I got one for accounts and one for assets

type Account struct {
    tableName struct{} `sql:"accounts,alias:t" pg:",discard_unknown_columns"`

    Address     string                 `sql:"address,pk"`
    Confirmed   bool                   `sql:"confirmed,pk"`
}

type Asset struct {
    tableName struct{} `sql:"assets,alias:t" pg:",discard_unknown_columns"`

    Confirmed    bool      `sql:"confirmed,pk"`
    ID           string    `sql:"id,pk"`
    OwnerAddress string    `sql:"owner_address,notnull"`

    ConfirmedRel    *Account     `pg:"fk:confirmed"`
    OwnerAddressRel *Account     `pg:"fk:owner_address"`
}

The issue is that if I insert Asset into pg using

var asset Asset
// Initialize asset ...
db.Insert(&asset)

I would get an error trying to insert confirmed_rel and owner_address_rel: ERROR #42703 column "confirmed_rel" of relation "assets" does not exist

If it was a single foreign key, go-pg/pg ignores the foreign key field and only inserts the valid confirmed and owner_address. The specific check to deny filtering composite key is at https://github.com/go-pg/pg/blob/master/orm/table.go#L922

---

So my question is whether genna should generate composite foreign keys in a different way so inserting with the auto-generated data works out-of-box. For now I am explicitly adding sql:"-" so it doesn't save. Alternatively, maybe not generate at all, or generate them in a different way for go-pg/pg to recognize? Thanks for any suggestions.

[dizzyfool]

Well, go-pg's readme states that:

ORM supports has one, belongs to, has many, and many to many with composite/multi-column primary keys.

It seems that your case should be supported by go-pg as you have

"accounts_pkey" PRIMARY KEY, btree (address, confirmed)

So I have to dig into this to understand how to handle composite keys in genna.

[dizzyfool]

I've tried several ways to describe foreign keys with multiple fields and got no success. I'll write to go-pg author directly and ask him about that, but I think there no possibility to define such model.

Anyway, I've fixed some bugs I found while trying make this work :)

[taiyangc]

Thank you for your effort! Yeah I agree some additional support from go-pg would be great.