What Are the Differences Between Unique Constraints and Unique Indexes in GaussDB(DWS)?

• 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.
  1. Both unique constraints and unique indexes can be used to ensure the uniqueness of column values which can be NULL.
  2. 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. GaussDB(DWS) row-store tables support unique constraints, but column-store tables do not.
  3. A created unique index is independent and can be deleted separately. Currently in GaussDB(DWS), unique indexes can only be created using B-Tree.
  4. 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.
  5. 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.

Example: Create a composite index for two columns, which is not required to be a unique index.

1
2

CREATE TABLE t (n1 number,n2 number,n3 number,PRIMARY KEY (n3));
CREATE INDEX t_idx ON t(n1,n2);

GaussDB (DWS) supports multiple unique indexes for a table.

1
2

CREATE UNIQUE INDEX u_index ON t(n3);
CREATE UNIQUE INDEX u_index1 ON t(n3);

You can use the index t_idx created in the example above to create a unique constraint t_uk, which is unique only on column n1. A unique constraint is stricter than a unique index.

1

ALTER TABLE t ADD CONSTRAINT t_uk UNIQUE USING INDEX u_index;