Key (Index) Specification

key_spec:
        [PRIMARY | UNIQUE] KEY [USING {B_TREE | HASH_TREE | R_TREE}]

PRIMARY KEY

Primary keys uniquely identify each the table row. Some of the characteristics of a PRIMARY KEY are:

  • Only one PRIMARY KEY column per table
  • Cannot be NULL
  • Value of column cannot be changed
  • R_TREE is not a supported index type for a PRIMARY KEY
  • ROWID columns MUST be designated as a PRIMARY KEY

UNIQUE KEY

Unique keys, similar to a PRIMARY KEY, cannot have duplicate values. However, a UNIQUE KEY can be NULL and can be updated to a new value as long as the new value is still unique in the current table.

For the purpose of a UNIQUE constraint, NULL values are not considered equal.

Index Types

RaimaDB provides several key (index) types: B_TREE, HASH_TREE or R_TREE.

B_TREE

B-tree indexes are the default index type for RaimaDB databases. The characteristics of the B-tree allow for sorted ordering in ascending or descending order. The B-tree also enables the cursor positioning modes of: less than (<); less than or equal (<=); equal (=); greater than or equal (>=); and, greater than (>).

HASH_TREE

The RaimaDB HASH_TREE index can only be used for equality cursor navigation and the index can only be used for PRIMARY KEY or UNIQUE keys. The implementation of the RaimaDB HASH_TREE index is not a traditional hash index. The implementation does calculate a hash value from the column data being indexed and the that value is then indexed using a B-tree method.

The HASH_TREE index is recommended for large compound keys or for larger CHAR columns keys where only an equality look-up is needed (no forward or backward cursor navigation).

R_TREE

RaimaDB supports the optimized storing of multi-dimensional data through an implementation of the R-tree index. Some characteristics of an R-tree column are:

  • The column must be an ARRAY of integer or real values. For more in formation on ARRAY types, refer to the Arrays section.
  • The R-tree index must be on a single column (no compound R-tree index definitions).
  • The R-tree specifies a low and high value in each dimension, therefore the column ARRAY dimension must be a multiple of two.
  • RaimaDB will support an R-tree with a maximum of four dimensions so the column array dimension must be either 2 (one dimension), 4 (two dimensions), 6 (three dimensions), or 8 (four dimensions).
  • The R-tree column cannot be a PRIMARY KEY for the table.

Valid R-tree index definitions in the schema:

i8 INT8 ARRAY[4] KEY USING R_TREE NOT NULL /* 2 dimensional signed 8-bit */
u16 UINT16 ARRAY[2] PRIMARY KEY USING R_TREE /* 1 dimensional unsigned 16-bit */
i32 INT32 ARRAY[6] UNIQUE KEY USING R_TREE NOT NULL /* 3 dimensional signed 32-bit */
u64 UINT64 ARRAY[8] PRIMARY KEY USING R_TREE NOT NULL /* 4 dimensional unsigned 64-bit */
dbl DOUBLE ARRAY[4] PRIMARY KEY USING R_TREE NOT NULL /* 2 dimensional double */
flt FLOAT ARRAY[2] KEY USING R_TREE NOT NULL /* 2 dimensional float */

For more information, refer to the R-tree Index Overview section.