What Are the Differences Between Unique Constraints and Unique Indexes?

Answer: The differences between unique constraints and unique indexes are as follows:

• The concepts of a unique constraint and a unique index are different.

  A unique constraint specifies that the values in a column or a group of columns are all unique. If DISTRIBUTE BY REPLICATION is not specified, the column table that contains only unique values must contain distribution columns. A unique index is used to ensure the uniqueness of a field value or the value combination of multiple fields. CREATE UNIQUE INDEX creates a unique index.

• The functions of a unique constraint and a unique index are different.

  Constraints are used to ensure data integrity, and indexes are used to facilitate query.

• The usages of a unique constraint and a unique index are different.
  • When a unique constraint is created, a unique index with the same name is automatically created. The index cannot be deleted separately. When the constraint is deleted, the index is automatically deleted. A unique constraint uses a unique index to ensure data uniqueness.
  • A created unique index is independent and can be deleted separately.
  • If you want to have both a unique constraint and a unique index on a column, and they can be deleted separately, you can create a unique index and then a unique constraint with the same name.
  • If a field in a table is to be used as a foreign key of another table, the field must have a unique constraint (or it is a primary key). If the field has only a unique index, an error is reported.