AutoMigrate with self-referential belongs to

[suciuvlad]

Your Question

I'm trying to perform AutoMigrate on a self-referential table where the model doesn't reference the primary key:

type Transaction struct {
	gorm.Model

	ExternalID   uint `gorm:"uniqueIndex"`
	ReferenceID *uint `json:"reference_id" gorm:"uniqueIndex; type:uuid"`
	Reference   *Transaction `gorm:"foreignKey:ReferenceID; References:ExternalID"`
}

this results in the following SQL query:
CREATE TABLE "transactions" ("id" bigserial,"created_at" timestamptz,"updated_at" timestamptz,"deleted_at" timestamptz,"reference_id" bigint,"external_id" bigint,PRIMARY KEY ("id"),CONSTRAINT "fk_transactions_reference" FOREIGN KEY ("reference_id") REFERENCES "transactions"("external_id"))

that returns the following error:
"ERROR: there is no unique constraint matching given keys for referenced table "transactions" -- This is due to the fact that the constraint expects an unique index on "external_id" that is only created after CREATE TABLE query.

Expected answer

1. Is it possible to skip creating constraints on specific columns without disabling it for all tables? (I'm aware of DisableForeignKeyConstraintWhenMigrating)
2. Should GORM's AutoMigrate create the uniqueIndex on external_id prior to creating the constraint? (for example creating the constraint after the table's been created)

@jinzhu Any thoughts?

[jinzhu]

Is it possible to skip creating constraints on specific columns without disabling it for all tables? (I'm aware of DisableForeignKeyConstraintWhenMigrating)

Initialize another DB with DisableForeignKeyConstraintWhenMigrating when migrating the model?

Should GORM's AutoMigrate create the uniqueIndex on external_id prior to creating the constraint? (for example creating the constraint after the table's been created)

Don't think this is a good idea, and you can just set the uniqueIndex tag for the field, and use AutoMigrate to create the unique index?

[suciuvlad]

@jinzhu I have already set uniqueIndex on the field. But constraints are created prior to indexes during CREATE TABLE so the query to create the table fails. In this test:

gorm/schema/relationship_test.go

Lines 58 to 70 in f6ed895

	func TestSelfReferentialBelongsToOverrideReferences(t *testing.T) {
	type User struct {
	ID int32 `gorm:"primaryKey"`
	Name string
	CreatedBy *int32
	Creator *User `gorm:"foreignKey:CreatedBy;references:ID"`
	}
	checkStructRelation(t, &User{}, Relation{
	Name: "Creator", Type: schema.BelongsTo, Schema: "User", FieldSchema: "User",
	References: []Reference{{"ID", "User", "CreatedBy", "User", "", false}},
	})
	}

references:ID is set to the primary key, but if one changes it to another ID in the same table, GORM would return an error during AutoMigrate.

Here's a link to the playground: go-gorm/playground#184

[jinzhu]

Hi @suciuvlad

You could use the unique tag and it creates unique constraint when creating table

type Transaction struct {
	gorm.Model

	ExternalID   uint `gorm:"unique"`
	ReferenceID *uint `json:"reference_id" gorm:"unique; type:uuid"`
	Reference   *Transaction `gorm:"foreignKey:ReferenceID; References:ExternalID"`
}

[suciuvlad]

@jinzhu that worked! Thank you very much.