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 of a non-unique index table are the user-specified indexed column and the primary key column in the primary table. 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.
In the following example, storage keys of the i1 table with a non-unique index are c2 and c1. The storage key of the i2 table with a unique index is c3, and c1 is variable. When c3 is NULL, the value of the variable c1 column is the same as that of the c1 column in the primary table. When c3 is not NULL, the value of the variable c1 column is NULL.
CREATE TABLE t1(c1 INT, c2 INT, c3 INT, c4 INT, PRIMARY KEY(c1));
CREATE INDEX i1 ON t1(c2);
CREATE UNIQUE INDEX i2 ON t1(c3);