How to stop Go gorm from forcing a not null constraint on my self referencing foreign key in Postgres

When using Go's GORM to connect to a Postgres database, issues with NOT NULL constraints on self-referencing foreign keys often arise because GORM automatically enforces the NOT NULL constraint on foreign key columns by default. This can cause problems in certain scenarios, especially when your model design includes optional self-referencing relationships.

For example, consider a simple employee management system where each employee can have an optional supervisor (i.e., a self-referencing foreign key). In this case, you want the supervisor ID (supervisor_id) to be nullable.

The model might resemble:

go
 type Employee struct {
    ID           uint
    Name         string
    SupervisorID *uint   // points to the same table, nullable
    Supervisor   *Employee `gorm:"foreignKey:SupervisorID;"`
}

To avoid this issue in GORM, implement the following steps:

1. Use pointers or sql.NullInt64: Define the associated ID field as a pointer type (*uint) or sql.NullInt64 to allow it to be NULL in the database.

2. Customize foreign key constraints: Specify the foreign key and its reference explicitly using GORM's tags foreignKey and references. While GORM typically handles this automatically, manual specification may be necessary in certain cases.

3. Set foreign key constraints during migration: When performing database migrations with GORM, ensure the foreign key column is created as nullable. If using GORM's AutoMigrate feature, it generally handles this automatically based on your model definition, but manual control may be required. For example:

go
 db.Migrator().CreateConstraint(&Employee{}, "Supervisor")
 db.Migrator().CreateConstraint(&Employee{}, "fk_employees_supervisor_id")

1. Handle NULL values during insertion and querying: In your application logic, manage possible NULL values, such as when creating new employee records without a supervisor.

Finally, test your model and database interactions to ensure everything works as expected. Create test cases, such as adding employees without a supervisor and adding employees with a supervisor, then query them to verify that the foreign key correctly handles NULL values. These methods should help you resolve the issue of GORM enforcing NOT NULL constraints on self-referencing foreign keys in Postgres.