Shulou(Shulou.com)06/01 Report--

This article mainly shows you the "GBase8s unique index and non-unique index problem example analysis", the content is easy to understand, well-organized, hope to help you solve your doubts, the following let the editor lead you to study and study the "GBase8s unique index and non-unique index problem example analysis" this article.

Unique indexes do not allow duplicate values in columns and can be used to define and constrain one or more column combinations of values in a table. Uniqueness needs to be checked when executing insert and update statements. The primary key (PRIMARY KEY) in GBase8s automatically creates a unique index. A good table design should define a primary key or a unique constraint index. Especially in OLTP systems, unique indexes can help quickly locate a small number of records.

The syntax for creating a unique index:

CREATE UNIQUE INDEX idx_name ON tabname (col)

Or

CREATE DISTINCT INDEX idx_name ON tabname (col)

A non-unique index (repeatable index) can be created in a non-primary key column, allowing duplicate data in the column. However, you need to avoid creating indexes on data columns where the subkeys are too duplicated, because the indexes with more duplicate values are less efficient.

Examples of unique and non-unique indexes are as follows:

Note: since uniqueness judgment is required in insert and update, it is not recommended to create multiple unique indexes on a table. To ensure uniqueness, it is generally sufficient to create a unique index in a table.

Supplement: spatial separation method used by GBase 8s database tables and primary key indexes

There are generally two ways for a GBase 8s database to create a primary key:

1. Specify the primary key when creating the table, such as:

Create table tab1 (id int, name varchar (255), primary key (id)) in datadbs01

2. Use the alter statement to modify the table structure to create a primary key

Create table tab1 (id int, name varchar (255)) in datadbs01;alter table tab1 add constraint primary key (id)

Neither of these methods can specify a separate space for the index automatically created by the primary key.

In fact, there is a flexible way to separate the space used by the table from the space used by the primary key index. You need to add a unique index before method 2 (that is, do not let alter table add primary key create the index automatically, but use the unique index you just created), as follows:

Create tables, create unique indexes using datadbs01 space create table tab1 (id int, name varchar) in datadbs01;--, modify tables using indexdbs01 space create unique index ix_tab1_id on tab1 (id) in indexdbs01;--, create primary key (associated unique index) alter table tab1 add constraint primary key (id) The above is all the contents of the article "sample Analysis of unique and non-unique Index problems in GBase8s". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.