Unique and non-unique indexes

Indexes are classified into unique and non-unique indexes. Unique indexes ensure that no two rows of a table have duplicate values in an indexed column. Non-unique indexes allow duplicate values in an indexed column. In OceanBase Database, NULL values are also stored in indexes.

Storage keys

• Non-unique indexes

  Storage keys of a non-unique index table are the user-specified indexed column and the primary key column in the primary table.

• Unique indexes

  Storage keys of a unique index table are the user-specified indexed column and a variable primary key column in the primary table. "Variable" means that values of the primary key column vary with the values of the indexed column.

  • When the indexed column is not NULL, the variable primary key column does not play a role and the value of the indexed column is used for storage.
  • When the indexed column is NULL, the variable primary key column is set to the same value as the primary key column in the primary table. This is used to distinguish between multiple NULL values in a unique index table.

Here is an example:

• For the idx1_tbl1 table with a non-unique index, the storage keys are col2 and col1.

• For the idx2_tbl1 table with a unique index:

  • When the indexed column col3 is NULL, the value of the variable col1 column is the same as that of the col1 column in the primary table. The storage key is col1.
  • When the indexed column col3 is not NULL, the value of the variable col1 column is NULL and does not play a role. The storage key is col3.

1. Create the tbl1 table.

   CREATE TABLE tbl1(col1 INT, col2 INT, col3 INT, col4 INT, PRIMARY KEY(col1));
   

2. Create the non-unique index idx1_tbl1.

   CREATE INDEX idx1_tbl1 ON tbl1(col2);
   

3. Create the unique index idx2_tbl1.

   CREATE UNIQUE INDEX idx2_tbl1 ON tbl1(col3);