Foreign Key Constraint in gorm

Question

I want to create a Booking-Class relationship, where every Booking can be assigned only one Class.

type Class struct {
    Id   int    `json:"id"`
    Name string `json:"name"`
}

type Booking struct {
    Id      int    `json:"id"`
    User    string `json:"user"`
    Members int    `json:"members"`
    ClassId int    `json:"classid"`
}

I understand that it is similar to gorm's "belongs-to" relationship explained here https://gorm.io/docs/belongs_to.html but I was wondering if it's possible to achieve "foreign key constraint" without defining the field of type Class inside the Booking model (only ClassId int). To make sure that non-existent ClassId-s are used I defined functions:

func Find(slice []int, val int) bool {
    for _, item := range slice {
        if item == val {
            return true
        }
    }
    return false
}

func GetClassKeys(d *gorm.DB) []int {

    var keys []int
    rows, _ := d.Raw(`SELECT id
                        FROM classes`).Rows()
    defer rows.Close()
    for rows.Next() {
        var key int
        rows.Scan(&key)
        keys = append(keys, key)
    }
    return keys
}

And perform this check before creating/updating a booking:

if !Find(GetClassKeys(db), booking.ClassId) {
    log.Println("Wrong class id value")
    return
}

But this doesn't handle the case of removed class id (which regular databases do automatically). I was wondering is there a way to achieve a normal database foreign key functionality with gorm by simply referencing the primary key of Class in a User model? Thanks in advance

Answer 1

I don't think the Migrator tool will help you because it assumes you're going to use the default Gorm patterns for defining relationships, and you've explicitly decided NOT to use these.

The only remaining option is to manage the constraint yourself, either through an SQL script or easier, some custom queries you run alongside your AutoMigrate call:

import "gorm.io/gorm/clause"

// somewhere you must be calling
db.AutoMigrate(&Class{}, &Booking{})

// then you'd have:
constraint := "fk_booking_classid"
var count int64
err := db.Raw(
    "SELECT count(*) FROM INFORMATION_SCHEMA.table_constraints WHERE constraint_schema = ? AND table_name = ? AND constraint_name = ?", 
    db.Migrator().CurrentDatabase(),
    "bookings",
    constraint,
).Scan(&count).Error
// handle error

if (count == 0) {
    err := db.Exec(
        "ALTER TABLE bookings ADD CONSTRAINT ? FOREIGN KEY class_id REFERENCES classes(id)",
        clause.Table{Name: constraint},
    ).Error
    // handle error
}

Of course, this negates the advantage of having an automated migration (which means when things change like the field name, the constraint will be updated without changes to the migration code).

I'd be looking at why you don't want to define the foreign key as gorm expects, as this smells of you trying to use the database model directly as your JSON API Request/Response model, and that usually doesn't lead to a good end :)