vpruthi/doc-exports
1
0
Fork 0
forked from docs/doc-exports
Code Pull Requests Activity
doc-exports/docs/dws/umn/dws_03_1002.html
luhuayi 85562e1879 DWS UMN 831.100 version 
Reviewed-by: Pruthi, Vineet <vineet.pruthi@t-systems.com>
Co-authored-by: luhuayi <luhuayi@huawei.com>
Co-committed-by: luhuayi <luhuayi@huawei.com>
2024-11-13 08:55:54 +00:00

7.0 KiB
Raw Permalink Blame History

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;
Parent topic: Database Usage