Repository: go-gorm/gorm
Pull request #6386 (2023-06-07 11:18:29)

refactor: distinguish between Unique and UniqueIndex
<!--
Make sure these boxes checked before submitting your pull request.

For significant changes, please open an issue to make an agreement on an implementation design/plan first before starting it.
-->

- [X] Do only one thing
- [X] Non breaking API changes
- [X] Tested

### What did this pull request do?

<!--
provide a general description of the code changes in your pull request
-->
See #6381

Related issues:
#6224
#5401
#5715
#5681
#6378
#6407

### User Case Description

<!-- Your use case -->


black-06: Can I use `"github.com/stretchr/testify/assert"` in "gorm.io/gorm/tests" ?
I need `ElementsMatch`, or copy code to tests package ?
a631807682: > Can I import `"github.com/stretchr/testify/assert"` to "gorm.io/gorm/tests" ? I need `ElementsMatch`, or copy code to tests package ?

Both ok for me.
black-06: What should we do with 
```
`gorm:"uniqueIndex;index"`
```
a631807682: > What should we do with
> 
> ```
> `gorm:"uniqueIndex;index"`
> ```

This situation is meaningless, it is equivalent to `gorm:"uniqueIndex;"`, but the following scenarios will exist
```
Name string `gorm:"uniqueIndex:idx_name_code"`
Code string `gorm:"uniqueIndex:idx_name_code;index"`
```
black-06: > > What should we do with
> > ```
> > `gorm:"uniqueIndex;index"`
> > ```
> 
> This situation is meaningless, it is equivalent to `gorm:"uniqueIndex;"`, but the following scenarios will exist
> 
> ```
> Name string `gorm:"uniqueIndex:idx_name_code"`
> Code string `gorm:"uniqueIndex:idx_name_code;index"`
> ```

In this case:
```go
type UserIndex struct {
	Name8        string `gorm:"index:,length:10;index:,collate:utf8"`
}
```

It got index 
```go
"idx_user_indices_name8": {
	Name: "idx_user_indices_name8",
	Type: "",
	Fields: []schema.IndexOption{
		{Field: &schema.Field{Name: "Name8"}, Length: 10},
		{Field: &schema.Field{Name: "Name8"}, Collate: "utf8"},
	},
}
```
Maybe Index.Fields ([]IndexOption) should be changed to Map[string]IndexOption which key is field name.

a631807682: > ```go
> type UserIndex struct {
> 	Name8        string `gorm:"index:,length:10;index:,collate:utf8"`
> }
> ```

The same index may indeed be created multiple times, but I am not sure whether the actual usage scenario exists. In this case, I can only think that some scenarios may need to be specified using FORCE INDEX, but in this case, I think users can specify an index name instead of using the default name.
In short, I think there are too few usage scenarios to consider this situation, of course if my thinking is wrong, please let me know.
black-06: > In short, I think there are too few usage scenarios to consider this situation

I mean, it's an unreported bug.
We don't have to  fix it (because there are very few usage scenarios).


What is your opinion?  fix or not?
a631807682: > > In short, I think there are too few usage scenarios to consider this situation
> 
> I mean, it's an unreported bug. We don't have to fix it (because there are very few usage scenarios).
> 
> What is your opinion? fix or not?

We don't have to fix it, specifying the index name can avoid.
black-06: > 1. We need to ensure that the field A marked as unique index is not `a varchar(20) unique` when creating table, but `UNIQUE INDEX idx_a (a)`

For `unique`, we should do
```sql
CREATE TABLE `index_tests` (
  `name` VARCHAR(10),
  UNIQUE KEY `uni_index_tests_name` (`name`)
);
```
or
```sql
CREATE TABLE `index_tests` (
  `name` VARCHAR(10),
  UNIQUE INDEX `idx_index_tests_name` (`name`)
);
```
There is no difference between them in mysql,
but I don't know the situation of other databases.

For option 1, I should add a `ParseUniqueConstraints` like `ParseCheckConstraints`.
For option 2, I should modify in `ParseIndexes`.
a631807682: > > 1. We need to ensure that the field A marked as unique index is not `a varchar(20) unique` when creating table, but `UNIQUE INDEX idx_a (a)`
> 
> For `unique`, we should do
> 
> ```sql
> CREATE TABLE `index_tests` (
>   `name` VARCHAR(10),
>   CONSTRAINT `uni_index_tests_name` UNIQUE (`name`)
> );
> ```
> 
> or
> 
> ```sql
> CREATE TABLE `index_tests` (
>   `name` VARCHAR(10),
>   UNIQUE INDEX `idx_index_tests_name` (`name`)
> );
> ```
> 
> There is no difference between them in mysql, but I don't know the situation of other databases.
> 
> For option 1, I should add a `ParseUniqueConstraints` like `ParseCheckConstraints`. For option 2, I should modify in `ParseIndexes`.

```go
type User struct {
	ID        uint `gorm:"primarykey"`
	U  string `gorm:"type:varchar(20);uniqueIndex"`
	UI string `gorm:"type:varchar(20);unique"`
}
```

Consistent with the current, we need to remove the redundant unique statement
```diff
CREATE TABLE `users` (
	`id` BIGINT UNSIGNED AUTO_INCREMENT,
	`u` VARCHAR ( 20 ),
-	`ui` VARCHAR ( 20 ) UNIQUE,
+	`ui` VARCHAR ( 20 ), 
	PRIMARY KEY ( `id` ),
INDEX `idx_users_deleted_at` ( `deleted_at` ),
UNIQUE INDEX `idx_users_u` ( `u` ))
```
a631807682: > > 1. We need to ensure that the field A marked as unique index is not `a varchar(20) unique` when creating table, but `UNIQUE INDEX idx_a (a)`
> 
> For `unique`, we should do
> 
> ```sql
> CREATE TABLE `index_tests` (
>   `name` VARCHAR(10),
>   CONSTRAINT `uni_index_tests_name` UNIQUE (`name`)
> );
> ```
> 
> or
> 
> ```sql
> CREATE TABLE `index_tests` (
>   `name` VARCHAR(10),
>   UNIQUE INDEX `idx_index_tests_name` (`name`)
> );
> ```
> 
> There is no difference between them in mysql, but I don't know the situation of other databases.
> 
> For option 1, I should add a `ParseUniqueConstraints` like `ParseCheckConstraints`. For option 2, I should modify in `ParseIndexes`.

Consistent with the current, we need to remove the redundant unique statement.

```go
type User struct {
	ID uint `gorm:"primarykey"`
	UI string `gorm:"type:varchar(20);uniqueIndex"`
	U  string `gorm:"type:varchar(20);unique"`
}
```

```diff
CREATE TABLE `users` (
	`id` BIGINT UNSIGNED AUTO_INCREMENT,
-	`ui` VARCHAR ( 20 ) UNIQUE,
+	`ui` VARCHAR ( 20 ),
	`u` VARCHAR ( 20 ) UNIQUE,
	PRIMARY KEY ( `id` ),
INDEX `idx_users_deleted_at` ( `deleted_at` ),
UNIQUE INDEX `idx_users_ui` ( `ui` ))
```
black-06: There is no doubt that we need to remove the redundant unique statement.
My question is, should we declare this field as `UNIQUE INDEX` or `UNIQUE CONSTRAINT` ?

```diff
+ UNIQUE INDEX `idx_users_ui` ( `ui` ))
or
+ CONSTRAINT `idx_users_ui` UNIQUE (`ui`)
```
a631807682: > There is no doubt that we need to remove the redundant unique statement. My question is, should we declare this field as `UNIQUE INDEX` or `UNIQUE CONSTRAINT` ?
> 
> ```diff
> + UNIQUE INDEX `idx_users_ui` ( `ui` ))
> or
> + CONSTRAINT `idx_users_ui` UNIQUE (`ui`)
> ```

```sql
 UNIQUE INDEX `idx_users_ui` ( `ui` ))
```
black-06: > ```sql
>  UNIQUE INDEX `idx_users_ui` ( `ui` ))
> ```

I think it should be a constraint.
If it's an index, then we distinguish them without meaning.
And I queried some [docs](https://www.w3schools.com/sql/sql_unique.asp), they said the `Unique` is constraint
a631807682: > > ```sql
> >  UNIQUE INDEX `idx_users_ui` ( `ui` ))
> > ```
> 
> I think it should be a constraint. If it's an index, then we distinguish them without meaning. And I queried some [docs](https://www.w3schools.com/sql/sql_unique.asp), they said the `Unique` is constraint

When the user uses the `uniqueIndex` tag, I think what the user wants to create is an index (even if they are the same in most cases), including when we define the `uniqueIndex` tag, we parse and create a `partial index`, which may fail when creating a unique constraint?

It's a `constraint` when the user uses the `unique` tag, but since we're currently only receiving `boolean` values, it doesn't make much sense to change it.

https://stackoverflow.com/questions/23542794/postgres-unique-constraint-vs-index
https://dba.stackexchange.com/questions/144/when-should-i-use-a-unique-constraint-instead-of-a-unique-index/147#147?newreg=e5609e35473c4d0b912f18996c349674
black-06: > When the user uses the uniqueIndex tag, I think what the user wants to create is an index (even if they are the same in most cases), including when we define the uniqueIndex tag, we parse and create a partial index, which may fail when creating a unique constraint?

For `uniqueIndex` tag, there is no doubt that we created UniqueIndex.

> It's a constraint when the user uses the unique tag

+1

> but since we're currently only receiving boolean values, it doesn't make much sense to change it.

So we use the `unique` tag as the unique index?
```diff
GORM tag: unique
- unique constraint
+ unique index
```
But as you shared said, they have some subtle differences, such as error code, foreign key. 

summary:
| GORM tag | DB | 
| -- | -- |
| unique | unique constraint (or index?) <br/>Here I think it should be a constraint | 
| uniqueIndex | unique index |
| index | index |
| index:,unique | unique index |

a631807682: > So we use the unique tag as the unique index?

It should be `unique constraint` , but since we don't support multiple columns and we don't support patch constraint , so I don't think it makes sense to change it, can we change it in a later version or PR?

https://github.com/go-gorm/gorm/issues/5559#issuecomment-1200718658
black-06: > can we change it in a later version or PR?

Okay.

> Or changing it now will make subsequent work becomes easier?

No, Just more clearly
a631807682: black-06 proposes that if unique is defined as unique index, it will be indistinguishable (and compared) in database query, and the database query result unique and uniqueIndex tags are the same, so unique needs to be changed into a constraint.
Can't resolve #5401

Record this comment.
starudream: Are there any plans for an upcoming release?
black-06: I found that ci of GORM and drivers are deadlocked, they will never succeed.

So I will split this mr.
black-06: I rolled back a small part of them, otherwise this mr would never have passed the ci.
After that, I will modify Dialectors' mr, 
when they are merged, I will reopen a new mr to apply the part that was rolled back.

Guys, now that ci is successful, let's review and merge it
a631807682: > I rolled back a small part of them, otherwise this mr would never have passed the ci. After that, I will modify Dialectors' mr, when they are merged, I will reopen a new mr to apply the part that was rolled back.
> 
> Guys, now that ci is successful, let's review and merge it

Can you rebase 9ad102b - ab75e42 to one commit? It has been too long since I approved these changes. I just want to see what has been changed after that.

black-06: @a631807682 I have compressed them, please take time to review~

Patch:
diff --git a/migrator/migrator.go b/migrator/migrator.go
index 64a5a4b..d97fbf3 100644
--- a/migrator/migrator.go
+++ b/migrator/migrator.go
@@ -110,15 +110,20 @@ func (m Migrator) FullDataTypeOf(field *schema.Field) (expr clause.Expr) {
 	return
 }
 
+func (m Migrator) GetQueryAndExecTx() (queryTx, execTx *gorm.DB) {
+	queryTx = m.DB.Session(&gorm.Session{})
+	execTx = queryTx
+	if m.DB.DryRun {
+		queryTx.DryRun = false
+		execTx = m.DB.Session(&gorm.Session{Logger: &printSQLLogger{Interface: m.DB.Logger}})
+	}
+	return queryTx, execTx
+}
+
 // AutoMigrate auto migrate values
 func (m Migrator) AutoMigrate(values ...interface{}) error {
 	for _, value := range m.ReorderModels(values, true) {
-		queryTx := m.DB.Session(&gorm.Session{})
-		execTx := queryTx
-		if m.DB.DryRun {
-			queryTx.DryRun = false
-			execTx = m.DB.Session(&gorm.Session{Logger: &printSQLLogger{Interface: m.DB.Logger}})
-		}
+		queryTx, execTx := m.GetQueryAndExecTx()
 		if !queryTx.Migrator().HasTable(value) {
 			if err := execTx.Migrator().CreateTable(value); err != nil {
 				return err
@@ -268,7 +273,7 @@ func (m Migrator) CreateTable(values ...interface{}) error {
 					}
 					if constraint := rel.ParseConstraint(); constraint != nil {
 						if constraint.Schema == stmt.Schema {
-							sql, vars := buildConstraint(constraint)
+							sql, vars := constraint.Build()
 							createTableSQL += sql + ","
 							values = append(values, vars...)
 						}
@@ -276,6 +281,11 @@ func (m Migrator) CreateTable(values ...interface{}) error {
 				}
 			}
 
+			for _, uni := range stmt.Schema.ParseUniqueConstraints() {
+				createTableSQL += "CONSTRAINT ? UNIQUE (?),"
+				values = append(values, clause.Column{Name: uni.Name}, clause.Expr{SQL: stmt.Quote(uni.Field.DBName)})
+			}
+
 			for _, chk := range stmt.Schema.ParseCheckConstraints() {
 				createTableSQL += "CONSTRAINT ? CHECK (?),"
 				values = append(values, clause.Column{Name: chk.Name}, clause.Expr{SQL: chk.Constraint})
@@ -439,6 +449,10 @@ func (m Migrator) RenameColumn(value interface{}, oldName, newName string) error
 
 // MigrateColumn migrate column
 func (m Migrator) MigrateColumn(value interface{}, field *schema.Field, columnType gorm.ColumnType) error {
+	if field.IgnoreMigration {
+		return nil
+	}
+
 	// found, smart migrate
 	fullDataType := strings.TrimSpace(strings.ToLower(m.DB.Migrator().FullDataTypeOf(field).SQL))
 	realDataType := strings.ToLower(columnType.DatabaseTypeName())
@@ -499,7 +513,7 @@ func (m Migrator) MigrateColumn(value interface{}, field *schema.Field, columnTy
 	}
 
 	// check unique
-	if unique, ok := columnType.Unique(); ok && unique != field.Unique {
+	if unique, ok := columnType.Unique(); ok && unique != (field.Unique || field.UniqueIndex != "") {
 		// not primary key
 		if !field.PrimaryKey {
 			alterColumn = true
@@ -630,37 +644,36 @@ func (m Migrator) DropView(name string) error {
 	return m.DB.Exec("DROP VIEW IF EXISTS ?", clause.Table{Name: name}).Error
 }
 
-func buildConstraint(constraint *schema.Constraint) (sql string, results []interface{}) {
-	sql = "CONSTRAINT ? FOREIGN KEY ? REFERENCES ??"
-	if constraint.OnDelete != "" {
-		sql += " ON DELETE " + constraint.OnDelete
-	}
-
-	if constraint.OnUpdate != "" {
-		sql += " ON UPDATE " + constraint.OnUpdate
-	}
-
-	var foreignKeys, references []interface{}
-	for _, field := range constraint.ForeignKeys {
-		foreignKeys = append(foreignKeys, clause.Column{Name: field.DBName})
-	}
-
-	for _, field := range constraint.References {
-		references = append(references, clause.Column{Name: field.DBName})
-	}
-	results = append(results, clause.Table{Name: constraint.Name}, foreignKeys, clause.Table{Name: constraint.ReferenceSchema.Table}, references)
-	return
-}
-
 // GuessConstraintAndTable guess statement's constraint and it's table based on name
-func (m Migrator) GuessConstraintAndTable(stmt *gorm.Statement, name string) (_ *schema.Constraint, _ *schema.Check, table string) {
+//
+// Deprecated: use GuessConstraintInterfaceAndTable instead.
+func (m Migrator) GuessConstraintAndTable(stmt *gorm.Statement, name string) (*schema.Constraint, *schema.CheckConstraint, string) {
+	constraint, table := m.GuessConstraintInterfaceAndTable(stmt, name)
+	switch c := constraint.(type) {
+	case *schema.Constraint:
+		return c, nil, table
+	case *schema.CheckConstraint:
+		return nil, c, table
+	default:
+		return nil, nil, table
+	}
+}
+
+// GuessConstraintInterfaceAndTable guess statement's constraint and it's table based on name
+// nolint:cyclop
+func (m Migrator) GuessConstraintInterfaceAndTable(stmt *gorm.Statement, name string) (_ schema.ConstraintInterface, table string) {
 	if stmt.Schema == nil {
-		return nil, nil, stmt.Table
+		return nil, stmt.Table
 	}
 
 	checkConstraints := stmt.Schema.ParseCheckConstraints()
 	if chk, ok := checkConstraints[name]; ok {
-		return nil, &chk, stmt.Table
+		return &chk, stmt.Table
+	}
+
+	uniqueConstraints := stmt.Schema.ParseUniqueConstraints()
+	if uni, ok := uniqueConstraints[name]; ok {
+		return &uni, stmt.Table
 	}
 
 	getTable := func(rel *schema.Relationship) string {
@@ -675,7 +688,7 @@ func (m Migrator) GuessConstraintAndTable(stmt *gorm.Statement, name string) (_
 
 	for _, rel := range stmt.Schema.Relationships.Relations {
 		if constraint := rel.ParseConstraint(); constraint != nil && constraint.Name == name {
-			return constraint, nil, getTable(rel)
+			return constraint, getTable(rel)
 		}
 	}
 
@@ -683,40 +696,39 @@ func (m Migrator) GuessConstraintAndTable(stmt *gorm.Statement, name string) (_
 		for k := range checkConstraints {
 			if checkConstraints[k].Field == field {
 				v := checkConstraints[k]
-				return nil, &v, stmt.Table
+				return &v, stmt.Table
+			}
+		}
+
+		for k := range uniqueConstraints {
+			if uniqueConstraints[k].Field == field {
+				v := uniqueConstraints[k]
+				return &v, stmt.Table
 			}
 		}
 
 		for _, rel := range stmt.Schema.Relationships.Relations {
 			if constraint := rel.ParseConstraint(); constraint != nil && rel.Field == field {
-				return constraint, nil, getTable(rel)
+				return constraint, getTable(rel)
 			}
 		}
 	}
 
-	return nil, nil, stmt.Schema.Table
+	return nil, stmt.Schema.Table
 }
 
 // CreateConstraint create constraint
 func (m Migrator) CreateConstraint(value interface{}, name string) error {
 	return m.RunWithValue(value, func(stmt *gorm.Statement) error {
-		constraint, chk, table := m.GuessConstraintAndTable(stmt, name)
-		if chk != nil {
-			return m.DB.Exec(
-				"ALTER TABLE ? ADD CONSTRAINT ? CHECK (?)",
-				m.CurrentTable(stmt), clause.Column{Name: chk.Name}, clause.Expr{SQL: chk.Constraint},
-			).Error
-		}
-
+		constraint, table := m.GuessConstraintInterfaceAndTable(stmt, name)
 		if constraint != nil {
 			vars := []interface{}{clause.Table{Name: table}}
 			if stmt.TableExpr != nil {
 				vars[0] = stmt.TableExpr
 			}
-			sql, values := buildConstraint(constraint)
+			sql, values := constraint.Build()
 			return m.DB.Exec("ALTER TABLE ? ADD "+sql, append(vars, values...)...).Error
 		}
-
 		return nil
 	})
 }
@@ -724,11 +736,9 @@ func (m Migrator) CreateConstraint(value interface{}, name string) error {
 // DropConstraint drop constraint
 func (m Migrator) DropConstraint(value interface{}, name string) error {
 	return m.RunWithValue(value, func(stmt *gorm.Statement) error {
-		constraint, chk, table := m.GuessConstraintAndTable(stmt, name)
+		constraint, table := m.GuessConstraintInterfaceAndTable(stmt, name)
 		if constraint != nil {
-			name = constraint.Name
-		} else if chk != nil {
-			name = chk.Name
+			name = constraint.GetName()
 		}
 		return m.DB.Exec("ALTER TABLE ? DROP CONSTRAINT ?", clause.Table{Name: table}, clause.Column{Name: name}).Error
 	})
@@ -739,11 +749,9 @@ func (m Migrator) HasConstraint(value interface{}, name string) bool {
 	var count int64
 	m.RunWithValue(value, func(stmt *gorm.Statement) error {
 		currentDatabase := m.DB.Migrator().CurrentDatabase()
-		constraint, chk, table := m.GuessConstraintAndTable(stmt, name)
+		constraint, table := m.GuessConstraintInterfaceAndTable(stmt, name)
 		if constraint != nil {
-			name = constraint.Name
-		} else if chk != nil {
-			name = chk.Name
+			name = constraint.GetName()
 		}
 
 		return m.DB.Raw(
diff --git a/schema/check.go b/schema/check.go
deleted file mode 100644
index 89e732d..0000000
--- a/schema/check.go
+++ /dev/null
@@ -1,35 +0,0 @@
-package schema
-
-import (
-	"regexp"
-	"strings"
-)
-
-// reg match english letters and midline
-var regEnLetterAndMidline = regexp.MustCompile("^[A-Za-z-_]+$")
-
-type Check struct {
-	Name       string
-	Constraint string // length(phone) >= 10
-	*Field
-}
-
-// ParseCheckConstraints parse schema check constraints
-func (schema *Schema) ParseCheckConstraints() map[string]Check {
-	checks := map[string]Check{}
-	for _, field := range schema.FieldsByDBName {
-		if chk := field.TagSettings["CHECK"]; chk != "" {
-			names := strings.Split(chk, ",")
-			if len(names) > 1 && regEnLetterAndMidline.MatchString(names[0]) {
-				checks[names[0]] = Check{Name: names[0], Constraint: strings.Join(names[1:], ","), Field: field}
-			} else {
-				if names[0] == "" {
-					chk = strings.Join(names[1:], ",")
-				}
-				name := schema.namer.CheckerName(schema.Table, field.DBName)
-				checks[name] = Check{Name: name, Constraint: chk, Field: field}
-			}
-		}
-	}
-	return checks
-}
diff --git a/schema/constraint.go b/schema/constraint.go
new file mode 100644
index 0000000..5f6beb8
--- /dev/null
+++ b/schema/constraint.go
@@ -0,0 +1,66 @@
+package schema
+
+import (
+	"regexp"
+	"strings"
+
+	"gorm.io/gorm/clause"
+)
+
+// reg match english letters and midline
+var regEnLetterAndMidline = regexp.MustCompile("^[A-Za-z-_]+$")
+
+type CheckConstraint struct {
+	Name       string
+	Constraint string // length(phone) >= 10
+	*Field
+}
+
+func (chk *CheckConstraint) GetName() string { return chk.Name }
+
+func (chk *CheckConstraint) Build() (sql string, vars []interface{}) {
+	return "CONSTRAINT ? CHECK (?)", []interface{}{clause.Column{Name: chk.Name}, clause.Expr{SQL: chk.Constraint}}
+}
+
+// ParseCheckConstraints parse schema check constraints
+func (schema *Schema) ParseCheckConstraints() map[string]CheckConstraint {
+	checks := map[string]CheckConstraint{}
+	for _, field := range schema.FieldsByDBName {
+		if chk := field.TagSettings["CHECK"]; chk != "" {
+			names := strings.Split(chk, ",")
+			if len(names) > 1 && regEnLetterAndMidline.MatchString(names[0]) {
+				checks[names[0]] = CheckConstraint{Name: names[0], Constraint: strings.Join(names[1:], ","), Field: field}
+			} else {
+				if names[0] == "" {
+					chk = strings.Join(names[1:], ",")
+				}
+				name := schema.namer.CheckerName(schema.Table, field.DBName)
+				checks[name] = CheckConstraint{Name: name, Constraint: chk, Field: field}
+			}
+		}
+	}
+	return checks
+}
+
+type UniqueConstraint struct {
+	Name  string
+	Field *Field
+}
+
+func (uni *UniqueConstraint) GetName() string { return uni.Name }
+
+func (uni *UniqueConstraint) Build() (sql string, vars []interface{}) {
+	return "CONSTRAINT ? UNIQUE (?)", []interface{}{clause.Column{Name: uni.Name}, clause.Column{Name: uni.Field.DBName}}
+}
+
+// ParseUniqueConstraints parse schema unique constraints
+func (schema *Schema) ParseUniqueConstraints() map[string]UniqueConstraint {
+	uniques := make(map[string]UniqueConstraint)
+	for _, field := range schema.Fields {
+		if field.Unique {
+			name := schema.namer.UniqueName(schema.Table, field.DBName)
+			uniques[name] = UniqueConstraint{Name: name, Field: field}
+		}
+	}
+	return uniques
+}
diff --git a/schema/field.go b/schema/field.go
index 657e0a4..91e4c0a 100644
--- a/schema/field.go
+++ b/schema/field.go
@@ -89,6 +89,12 @@ type Field struct {
 	Set                    func(context.Context, reflect.Value, interface{}) error
 	Serializer             SerializerInterface
 	NewValuePool           FieldNewValuePool
+
+	// In some db (e.g. MySQL), Unique and UniqueIndex are indistinguishable.
+	// When a column has a (not Mul) UniqueIndex, Migrator always reports its gorm.ColumnType is Unique.
+	// It causes field unnecessarily migration.
+	// Therefore, we need to record the UniqueIndex on this column (exclude Mul UniqueIndex) for MigrateColumnUnique.
+	UniqueIndex string
 }
 
 func (field *Field) BindName() string {
diff --git a/schema/index.go b/schema/index.go
index f5ac5dd..f4f3675 100644
--- a/schema/index.go
+++ b/schema/index.go
@@ -13,8 +13,8 @@ type Index struct {
 	Type    string // btree, hash, gist, spgist, gin, and brin
 	Where   string
 	Comment string
-	Option  string // WITH PARSER parser_name
-	Fields  []IndexOption
+	Option  string        // WITH PARSER parser_name
+	Fields  []IndexOption // Note: IndexOption's Field maybe the same
 }
 
 type IndexOption struct {
@@ -67,7 +67,7 @@ func (schema *Schema) ParseIndexes() map[string]Index {
 	}
 	for _, index := range indexes {
 		if index.Class == "UNIQUE" && len(index.Fields) == 1 {
-			index.Fields[0].Field.Unique = true
+			index.Fields[0].Field.UniqueIndex = index.Name
 		}
 	}
 	return indexes
diff --git a/schema/interfaces.go b/schema/interfaces.go
index a75a33c..306d4f4 100644
--- a/schema/interfaces.go
+++ b/schema/interfaces.go
@@ -4,6 +4,12 @@ import (
 	"gorm.io/gorm/clause"
 )
 
+// ConstraintInterface database constraint interface
+type ConstraintInterface interface {
+	GetName() string
+	Build() (sql string, vars []interface{})
+}
+
 // GormDataTypeInterface gorm data type interface
 type GormDataTypeInterface interface {
 	GormDataType() string
diff --git a/schema/relationship.go b/schema/relationship.go
index 5716785..2e94fc2 100644
--- a/schema/relationship.go
+++ b/schema/relationship.go
@@ -605,6 +605,7 @@ func (schema *Schema) guessRelation(relation *Relationship, field *Field, cgl gu
 	}
 }
 
+// Constraint is ForeignKey Constraint
 type Constraint struct {
 	Name            string
 	Field           *Field
@@ -616,6 +617,31 @@ type Constraint struct {
 	OnUpdate        string
 }
 
+func (constraint *Constraint) GetName() string { return constraint.Name }
+
+func (constraint *Constraint) Build() (sql string, vars []interface{}) {
+	sql = "CONSTRAINT ? FOREIGN KEY ? REFERENCES ??"
+	if constraint.OnDelete != "" {
+		sql += " ON DELETE " + constraint.OnDelete
+	}
+
+	if constraint.OnUpdate != "" {
+		sql += " ON UPDATE " + constraint.OnUpdate
+	}
+
+	foreignKeys := make([]interface{}, 0, len(constraint.ForeignKeys))
+	for _, field := range constraint.ForeignKeys {
+		foreignKeys = append(foreignKeys, clause.Column{Name: field.DBName})
+	}
+
+	references := make([]interface{}, 0, len(constraint.References))
+	for _, field := range constraint.References {
+		references = append(references, clause.Column{Name: field.DBName})
+	}
+	vars = append(vars, clause.Table{Name: constraint.Name}, foreignKeys, clause.Table{Name: constraint.ReferenceSchema.Table}, references)
+	return
+}
+
 func (rel *Relationship) ParseConstraint() *Constraint {
 	str := rel.Field.TagSettings["CONSTRAINT"]
 	if str == "-" {


Test patch:
diff --git a/schema/check_test.go b/schema/constraint_test.go
similarity index 59%
rename from schema/check_test.go
rename to schema/constraint_test.go
index eda043b..6fcb1b8 100644
--- a/schema/check_test.go
+++ b/schema/constraint_test.go
@@ -6,6 +6,7 @@ import (
 	"testing"
 
 	"gorm.io/gorm/schema"
+	"gorm.io/gorm/utils/tests"
 )
 
 type UserCheck struct {
@@ -20,7 +21,7 @@ func TestParseCheck(t *testing.T) {
 		t.Fatalf("failed to parse user check, got error %v", err)
 	}
 
-	results := map[string]schema.Check{
+	results := map[string]schema.CheckConstraint{
 		"name_checker": {
 			Name:       "name_checker",
 			Constraint: "name <> 'jinzhu'",
@@ -53,3 +54,31 @@ func TestParseCheck(t *testing.T) {
 		}
 	}
 }
+
+func TestParseUniqueConstraints(t *testing.T) {
+	type UserUnique struct {
+		Name1 string `gorm:"unique"`
+		Name2 string `gorm:"uniqueIndex"`
+	}
+
+	user, err := schema.Parse(&UserUnique{}, &sync.Map{}, schema.NamingStrategy{})
+	if err != nil {
+		t.Fatalf("failed to parse user unique, got error %v", err)
+	}
+	constraints := user.ParseUniqueConstraints()
+
+	results := map[string]schema.UniqueConstraint{
+		"uni_user_uniques_name1": {
+			Name:  "uni_user_uniques_name1",
+			Field: &schema.Field{Name: "Name1", Unique: true},
+		},
+	}
+	for k, result := range results {
+		v, ok := constraints[k]
+		if !ok {
+			t.Errorf("Failed to found unique constraint %v from parsed constraints %+v", k, constraints)
+		}
+		tests.AssertObjEqual(t, result, v, "Name")
+		tests.AssertObjEqual(t, result.Field, v.Field, "Name", "Unique", "UniqueIndex")
+	}
+}
diff --git a/schema/index_test.go b/schema/index_test.go
index 890327d..2f1e36a 100644
--- a/schema/index_test.go
+++ b/schema/index_test.go
@@ -1,11 +1,11 @@
 package schema_test
 
 import (
-	"reflect"
 	"sync"
 	"testing"
 
 	"gorm.io/gorm/schema"
+	"gorm.io/gorm/utils/tests"
 )
 
 type UserIndex struct {
@@ -19,6 +19,7 @@ type UserIndex struct {
 	OID          int64  `gorm:"index:idx_id;index:idx_oid,unique"`
 	MemberNumber string `gorm:"index:idx_id,priority:1"`
 	Name7        string `gorm:"index:type"`
+	Name8        string `gorm:"index:,length:10;index:,collate:utf8"`
 
 	// Composite Index: Flattened structure.
 	Data0A string `gorm:"index:,composite:comp_id0"`
@@ -65,7 +66,7 @@ func TestParseIndex(t *testing.T) {
 		"idx_name": {
 			Name:   "idx_name",
 			Class:  "UNIQUE",
-			Fields: []schema.IndexOption{{Field: &schema.Field{Name: "Name2", Unique: true}}},
+			Fields: []schema.IndexOption{{Field: &schema.Field{Name: "Name2", UniqueIndex: "idx_name"}}},
 		},
 		"idx_user_indices_name3": {
 			Name:  "idx_user_indices_name3",
@@ -81,7 +82,7 @@ func TestParseIndex(t *testing.T) {
 		"idx_user_indices_name4": {
 			Name:   "idx_user_indices_name4",
 			Class:  "UNIQUE",
-			Fields: []schema.IndexOption{{Field: &schema.Field{Name: "Name4", Unique: true}}},
+			Fields: []schema.IndexOption{{Field: &schema.Field{Name: "Name4", UniqueIndex: "idx_user_indices_name4"}}},
 		},
 		"idx_user_indices_name5": {
 			Name:    "idx_user_indices_name5",
@@ -102,18 +103,27 @@ func TestParseIndex(t *testing.T) {
 		},
 		"idx_id": {
 			Name:   "idx_id",
-			Fields: []schema.IndexOption{{Field: &schema.Field{Name: "MemberNumber"}}, {Field: &schema.Field{Name: "OID", Unique: true}}},
+			Fields: []schema.IndexOption{{Field: &schema.Field{Name: "MemberNumber"}}, {Field: &schema.Field{Name: "OID", UniqueIndex: "idx_oid"}}},
 		},
 		"idx_oid": {
 			Name:   "idx_oid",
 			Class:  "UNIQUE",
-			Fields: []schema.IndexOption{{Field: &schema.Field{Name: "OID", Unique: true}}},
+			Fields: []schema.IndexOption{{Field: &schema.Field{Name: "OID", UniqueIndex: "idx_oid"}}},
 		},
 		"type": {
 			Name:   "type",
 			Type:   "",
 			Fields: []schema.IndexOption{{Field: &schema.Field{Name: "Name7"}}},
 		},
+		"idx_user_indices_name8": {
+			Name: "idx_user_indices_name8",
+			Type: "",
+			Fields: []schema.IndexOption{
+				{Field: &schema.Field{Name: "Name8"}, Length: 10},
+				// Note: Duplicate Columns
+				{Field: &schema.Field{Name: "Name8"}, Collate: "utf8"},
+			},
+		},
 		"idx_user_indices_comp_id0": {
 			Name: "idx_user_indices_comp_id0",
 			Type: "",
@@ -146,40 +156,109 @@ func TestParseIndex(t *testing.T) {
 		},
 	}
 
-	indices := user.ParseIndexes()
+	CheckIndices(t, results, user.ParseIndexes())
+}
 
-	for k, result := range results {
-		v, ok := indices[k]
-		if !ok {
-			t.Fatalf("Failed to found index %v from parsed indices %+v", k, indices)
-		}
+func TestParseIndexWithUniqueIndexAndUnique(t *testing.T) {
+	type IndexTest struct {
+		FieldA string `gorm:"unique;index"` // unique and index
+		FieldB string `gorm:"unique"`       // unique
 
-		for _, name := range []string{"Name", "Class", "Type", "Where", "Comment", "Option"} {
-			if reflect.ValueOf(result).FieldByName(name).Interface() != reflect.ValueOf(v).FieldByName(name).Interface() {
-				t.Errorf(
-					"index %v %v should equal, expects %v, got %v",
-					k, name, reflect.ValueOf(result).FieldByName(name).Interface(), reflect.ValueOf(v).FieldByName(name).Interface(),
-				)
-			}
-		}
+		FieldC string `gorm:"index:,unique"`     // uniqueIndex
+		FieldD string `gorm:"uniqueIndex;index"` // uniqueIndex and index
 
-		for idx, ef := range result.Fields {
-			rf := v.Fields[idx]
-			if rf.Field.Name != ef.Field.Name {
-				t.Fatalf("index field should equal, expects %v, got %v", rf.Field.Name, ef.Field.Name)
+		FieldE1 string `gorm:"uniqueIndex:uniq_field_e1_e2"` // mul uniqueIndex
+		FieldE2 string `gorm:"uniqueIndex:uniq_field_e1_e2"`
+
+		FieldF1 string `gorm:"uniqueIndex:uniq_field_f1_f2;index"` // mul uniqueIndex and index
+		FieldF2 string `gorm:"uniqueIndex:uniq_field_f1_f2;"`
+
+		FieldG string `gorm:"unique;uniqueIndex"` // unique and uniqueIndex
+
+		FieldH1 string `gorm:"unique;uniqueIndex:uniq_field_h1_h2"` // unique and mul uniqueIndex
+		FieldH2 string `gorm:"uniqueIndex:uniq_field_h1_h2"`        // unique and mul uniqueIndex
+	}
+	indexSchema, err := schema.Parse(&IndexTest{}, &sync.Map{}, schema.NamingStrategy{})
+	if err != nil {
+		t.Fatalf("failed to parse user index, got error %v", err)
+	}
+	indices := indexSchema.ParseIndexes()
+	CheckIndices(t, map[string]schema.Index{
+		"idx_index_tests_field_a": {
+			Name:   "idx_index_tests_field_a",
+			Fields: []schema.IndexOption{{Field: &schema.Field{Name: "FieldA", Unique: true}}},
+		},
+		"idx_index_tests_field_c": {
+			Name:   "idx_index_tests_field_c",
+			Class:  "UNIQUE",
+			Fields: []schema.IndexOption{{Field: &schema.Field{Name: "FieldC", UniqueIndex: "idx_index_tests_field_c"}}},
+		},
+		"idx_index_tests_field_d": {
+			Name:  "idx_index_tests_field_d",
+			Class: "UNIQUE",
+			Fields: []schema.IndexOption{
+				{Field: &schema.Field{Name: "FieldD"}},
+				// Note: Duplicate Columns
+				{Field: &schema.Field{Name: "FieldD"}},
+			},
+		},
+		"uniq_field_e1_e2": {
+			Name:  "uniq_field_e1_e2",
+			Class: "UNIQUE",
+			Fields: []schema.IndexOption{
+				{Field: &schema.Field{Name: "FieldE1"}},
+				{Field: &schema.Field{Name: "FieldE2"}},
+			},
+		},
+		"idx_index_tests_field_f1": {
+			Name:   "idx_index_tests_field_f1",
+			Fields: []schema.IndexOption{{Field: &schema.Field{Name: "FieldF1"}}},
+		},
+		"uniq_field_f1_f2": {
+			Name:  "uniq_field_f1_f2",
+			Class: "UNIQUE",
+			Fields: []schema.IndexOption{
+				{Field: &schema.Field{Name: "FieldF1"}},
+				{Field: &schema.Field{Name: "FieldF2"}},
+			},
+		},
+		"idx_index_tests_field_g": {
+			Name:   "idx_index_tests_field_g",
+			Class:  "UNIQUE",
+			Fields: []schema.IndexOption{{Field: &schema.Field{Name: "FieldG", Unique: true, UniqueIndex: "idx_index_tests_field_g"}}},
+		},
+		"uniq_field_h1_h2": {
+			Name:  "uniq_field_h1_h2",
+			Class: "UNIQUE",
+			Fields: []schema.IndexOption{
+				{Field: &schema.Field{Name: "FieldH1", Unique: true}},
+				{Field: &schema.Field{Name: "FieldH2"}},
+			},
+		},
+	}, indices)
+}
+
+func CheckIndices(t *testing.T, expected, actual map[string]schema.Index) {
+	for k, ei := range expected {
+		t.Run(k, func(t *testing.T) {
+			ai, ok := actual[k]
+			if !ok {
+				t.Errorf("expected index %q but actual missing", k)
+				return
 			}
-			if rf.Field.Unique != ef.Field.Unique {
-				t.Fatalf("index field '%s' should equal, expects %v, got %v", rf.Field.Name, rf.Field.Unique, ef.Field.Unique)
+			tests.AssertObjEqual(t, ai, ei, "Name", "Class", "Type", "Where", "Comment", "Option")
+			if len(ei.Fields) != len(ai.Fields) {
+				t.Errorf("expected index %q field length is %d but actual %d", k, len(ei.Fields), len(ai.Fields))
+				return
 			}
-
-			for _, name := range []string{"Expression", "Sort", "Collate", "Length"} {
-				if reflect.ValueOf(ef).FieldByName(name).Interface() != reflect.ValueOf(rf).FieldByName(name).Interface() {
-					t.Errorf(
-						"index %v field #%v's %v should equal, expects %v, got %v", k, idx+1, name,
-						reflect.ValueOf(ef).FieldByName(name).Interface(), reflect.ValueOf(rf).FieldByName(name).Interface(),
-					)
-				}
+			for i, ef := range ei.Fields {
+				af := ai.Fields[i]
+				tests.AssertObjEqual(t, af, ef, "Name", "Unique", "UniqueIndex", "Expression", "Sort", "Collate", "Length")
 			}
-		}
+		})
+		delete(actual, k)
+	}
+	for k := range actual {
+		t.Errorf("unexpected index %q", k)
 	}
 }