Newer versions of GORM remove the unique index incorrectly

[Ponywka]

GORM Playground Link

<I have no time for write tests for your "playground" while working at office, sorry. Maybe later>

Description

I use gorm.io/driver/postgres v1.5.9, gorm.io/gorm v1.25.11 and go 1.22.6 in our project.

Sample environment:

type Smth struct {
	ID string `gorm:"uniqueIndex"`
	SmthDifferentID *int64 `gorm:"uniqueIndex"`
}

db, err := gorm.Open(postgres.Open(dsn), &gorm.Config{
	NamingStrategy: schema.NamingStrategy{TablePrefix: schemaName + "."},
})
if err != nil {
	return fmt.Errorf("database connecting error: %w", err)
}

err = db.Exec("CREATE SCHEMA IF NOT EXISTS " + schemaName).Error
if err != nil {
	return fmt.Errorf("creating schema error: %w", err)
}

err = db.AutoMigrate(&Smth{})
if err != nil {
	return fmt.Errorf("models automigrating error: %w", err)
}

When I run this code in already initialized before database in older versions of GORM I got this (place SmthDifferentID instead of FIELD):

[rows:0] ALTER TABLE "<SERVICE>"."<TABLE>" DROP CONSTRAINT "uni_<TABLE>_<FIELD>"
ERR: models automigrating error: ERROR: constraint "uni_<TABLE>_<FIELD>" of relation "<TABLE>" does not exist (SQLSTATE 42704)

After researching the issue, it was discovered that I have an existing record:

ALTER TABLE ONLY "<SERVICE>"."<TABLE>" ADD CONSTRAINT idx_<TABLE>_<FIELD> UNIQUE (<FIELD>);

GORM tries delete the uni_... index instead of idx_...

[LynxUA]

It's the same issue for me. It is introduced in 1.5.7 and caused by the changes in https://github.com/go-gorm/gorm/pull/6822

[alexei-g-aloteq]

It's the same issue for me. It is introduced in 1.5.7 and caused by the changes in go-gorm/gorm#6822

I had to fallback to:

gorm.io/driver/postgres v1.5.4
gorm.io/gorm v1.25.5

[LynxUA]

It's the same issue for me. It is introduced in 1.5.7 and caused by the changes in go-gorm/gorm#6822

I had to fallback to:

gorm.io/driver/postgres v1.5.4
gorm.io/gorm v1.25.5

My bad. I wanted to specify gorm.io/gorm v1.25.7

[Coartix]

Hello, Any update on this @jinzhu ? Please

[nguyentuan1696]

I have the same issue, any solution ???

[Antohnio123]

Same mistake. Is it going to be fixed soon? gorm.io/gorm v1.30.0 gorm.io/driver/postgres v1.6.0

[logrusorgru]

Inspecting the tables.

The new GORM:

    "idx_table_field" UNIQUE, btree (uuid)

And the old GORM

    "idx_table_field" UNIQUE CONSTRAINT, btree (uuid)

May be this error exists because of the CONSTRAINT word.

[logrusorgru]

Also, it shouldn't drop the constraint.

[d1ss0nanz]

The issue is, that the DB schema has a UNIQUE CONSTRAINT, but the gorm annotation declares a uniqueIndex.

So now that gorm differentiates between the two, the migration wants to do:

BEGIN;
ALTER TABLE <TABLE> DROP CONSTRAINT uni_<TABLE>_<FIELD>;
CREATE UNIQUE INDEX idx_<TABLE>_<FIELD> ON <TABLE> (<FIELD>);
COMMIT;

But the UNIQUE CONSTRAINT has the old idx_ prefix!

To fix the issue, just write a manual migration:

BEGIN;
-- Take note of the idx_ prefix
ALTER TABLE <TABLE> DROP CONSTRAINT idx_<TABLE>_<FIELD>;
CREATE UNIQUE INDEX idx_<TABLE>_<FIELD> ON <TABLE> (<FIELD>);
COMMIT;