Describe the feature

Distinguish between unique index and index to better support them.

Motivation

Because we can't distinguish them at present, it causes such as https://github.com/go-gorm/gorm/issues/6224#issuecomment-1547623416, and column will not be able to set unique index and index at the same time

The following tasks are open

  • [x] Distinguish between unique index and index in gorm @black-06
  • [x] Distinguish between unique index and index in postgres @black-06
  • [x] Distinguish between unique index and index in sqlserver @black-06
  • [x] Distinguish between unique index and index in sqlite @black-06
  • [x] Distinguish between unique index and index in mysql @black-06

If anyone is interested in the above tasks, please leave a message in the comments.

Related Issues

related issues: https://github.com/go-gorm/gorm/issues/6224 https://github.com/go-gorm/gorm/issues/5401 https://github.com/go-gorm/gorm/issues/5715 https://github.com/go-gorm/gorm/issues/5681 https://github.com/go-gorm/gorm/issues/6378 https://github.com/go-gorm/gorm/issues/6407 related pull request: https://github.com/go-gorm/gorm/pull/5341

Comment From: black-06

Let me do the distinguish in gorm (I touched the checkbox by mistake

Comment From: black-06

Is the index created repeatedly because of this? (#5715 & #6224) Or is there any fix in Dialector ?

Here's a playground I created: https://github.com/go-gorm/playground/pull/609 It says no duplicate indexes are created.

Comment From: a631807682

Is the index created repeatedly because of this? (#5715 & #6224) Or is there any fix in Dialector ?

Here's a playground I created: go-gorm/playground#609 It says no duplicate indexes are created.

Here is a mysql example

type Test struct {
    ID   uint   `gorm:"primaryKey"`
    Name string `gorm:"type:varchar(10);uniqueIndex"`
}

type Test1 struct {
    ID   uint   `gorm:"primaryKey"`
    Name string `gorm:"type:varchar(20);uniqueIndex"`
}

func TestGORM(t *testing.T) {
    DB.Migrator().DropTable(&Test{})
    DB.AutoMigrate(&Test{})
    DB.Table("tests").AutoMigrate(&Test1{})

    indexs, err := DB.Migrator().GetIndexes(&Test{})
    if err != nil {
        t.Fatal(err)
    }

    if len(indexs) != 2 { // pk and unique index
        t.Fatalf("should have one index, but got %v", len(indexs))
    }
}

[rows:0] ALTER TABLE `tests` MODIFY COLUMN `name` varchar(20) UNIQUE
[rows:0] ALTER TABLE `tests` MODIFY COLUMN `name` varchar(10) UNIQUE

We also need to pay attention to such scenarios

type Test2 struct {
    ID   uint   `gorm:"primaryKey"`
    Name string `gorm:"type:varchar(20);uniqueIndex:uniq_name_code;index"` // index and uniqueIndex
    Code string `gorm:"type:varchar(20);uniqueIndex:uniq_name_code"`
}

Comment From: a631807682

There is another situation here. Since unique and unique index cannot be distinguished, Description will be specified as unique when table create (will create description index), which leads to a conflict with partial Indexes user_description_exclude_deleted.

https://github.com/go-gorm/gorm/issues/6378

Comment From: black-06

I will continue to work on mysql and postgres

Comment From: black-06

When all the drivers are completed and tested, I can confirm that the changes in gorm are not breaking.

So continue to do sqlite and sqlserver...

Comment From: a631807682

When all the drivers are completed and tested, I can confirm that the changes in gorm are not breaking.

So continue to do sqlite and sqlserver...

Thank you for your contribution

Comment From: vijaykramesh

any update on this? I can confirm from black-06's branch and the postgres driver it works for me, in the meantime we can't upgrade gorm beyond 1.24.2 due to this issue (but would like to do so in order to get some nested join fixes)

Comment From: a631807682

@jinzhu @saeidee cc

Comment From: black-06

I am pleased to announce that we have completed the work.

But note that gorm v1.25.7 should work with (or higher version) sqlite v1.5.5, mysql v1.5.4, postgres v1.5.6, sqlserver v1.5.3

otherwise there may be some unexpected problems.

Similarly, these (or higher) versions of drivers require gorm v1.25.7 (or higher)