Unique and non-unique indexes

An index table can be a unique index table or a non-unique index table. In a unique index table, the values of the indexed columns are unique. In a non-unique index table, the values of the indexed columns are not unique. In OceanBase Database, NULL values are also stored in the index table.

Storage keys

• Non-unique index table

  For a non-unique index table, the storage key is the combination of the indexed columns and the primary key columns of the base table.

• Unique index table

  For a unique index table, the storage key is the combination of the indexed columns and the primary key columns of the base table. The primary key columns of the base table are variable, which means that their values vary based on the values of the indexed columns:

  • If the indexed columns are not NULL, the primary key columns of the base table are not used. The values of the indexed columns are used for storage.
  • If the indexed columns are NULL, the primary key columns of the base table are set to the same values as the primary key columns of the base table. This is used to distinguish between multiple NULL values in the unique index table.

Here is an example:

• For the non-unique index table idx1_tbl1, the storage key is the combination of col2 and col1.

• For the unique index table idx2_tbl1, the storage key is:

  • If the indexed column col3 is NULL, the variable col1 column value is the same as the primary key column col1 of the base table. The storage key is col1.
  • If the indexed column col3 is not NULL, the variable col1 column value 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);