How to set default behavior on delete?

Question

I am currently trying to make the constraints for my database and have a problem that I cant fix. I have 2 tables (user and role) which have a 1:n connection. The goal is that if I delete a Role the roleID on the user table is set to the default value (which would be 1). But when I run the code the role is set to null.

This is the scirpt I used for testing:

package main

import (
    "fmt"
    "log"

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

type Role struct {
    gorm.Model
    Name string
}

type User struct {
    gorm.Model
    Name   string
    RoleID uint
    Role   Role `gorm:"constraint:OnDelete:SET DEFAULT;"`
}

func main() {
    // Database connection
    dsn := "host=localhost user=postgres password=postgres dbname=testdb port=5433 sslmode=disable TimeZone=UTC"
    db, err := gorm.Open(postgres.Open(dsn), &gorm.Config{})
    if err != nil {
        panic("failed to connect to the database")
    }

    // Drop both tables for clean data
    db.Migrator().DropTable(&User{})
    db.Migrator().DropTable(&Role{})

    // Create the tables
    err = db.AutoMigrate(&Role{}, &User{})
    if err != nil {
        log.Fatalf("Error during AutoMigrate: %v", err)
    }

    // Create roles
    role1 := Role{Name: "user"}
    role2 := Role{Name: "admin"}
    db.Create(&role1)
    db.Create(&role2)

    // Create users and assign roles
    user1 := User{Name: "Alice", RoleID: role1.ID}
    user2 := User{Name: "Bob", RoleID: role2.ID}
    db.Create(&user1)
    db.Create(&user2)

    // Print initial state
    fmt.Println("Before deleting role:")
    printUsers(db)

    // Delete the admin role
    db.Delete(&role2)

    // Print final state
    fmt.Println("After deleting 'User' role:")
    printUsers(db)
}

// Helper function to print all users with their roles
func printUsers(db *gorm.DB) {
    var users []User
    db.Preload("Role").Find(&users)
    for _, user := range users {
        fmt.Printf("User: %s, Role: %s\n", user.Name, user.Role.Name)
    }
}

Answer 1

You need to set default value for User.RoleID to 1, here is example how to do that.

type User struct {
    Id     int    `gorm:"column:id"`
    RoleID int    `gorm:"default:1;column:role_id"`
    Name   string `gorm:"column:name"`
    Role   Role
}

type Role struct {
    Id    int    `gorm:"column:id"`
    Name  string `gorm:"column:name"`
    Users []User `gorm:"constraint:OnDelete:SET DEFAULT;foreignKey:RoleID;references:Id"`
}

in order to get DDL query like below

CREATE TABLE public.roles (
    id bigserial NOT NULL,
    name text NULL,
    CONSTRAINT roles_pkey PRIMARY KEY (id)
);

CREATE TABLE public.users (
    id bigserial NOT NULL,
    role_id int8 NULL DEFAULT 1,
    "name" text NULL,
    CONSTRAINT users_pkey PRIMARY KEY (id),
    CONSTRAINT fk_roles_users FOREIGN KEY (role_id) REFERENCES public.roles(id) ON DELETE SET DEFAULT
);

Demo

In addition:
I discovered that if you use gorm.Model, you need to perform a hard delete on the role. This is because when you soft delete a role, it remains in the database but is marked as deleted. PostgreSQL doesn’t automatically exclude soft-deleted roles when performing queries, so any associated users might still reference the 'deleted' role. As a result, the users aren't updated to reflect the deletion.