Specifying Unenforced Unique Constraints

You can specify the UNENFORCED option on a UNIQUE constraint to disable enforcement of the constraint. This means that SingleStore does not prevent you from inserting duplicate rows, and cannot guarantee that the constraint is true. An unenforced unique constraint is informational: the query planner may use the unenforced unique constraint as a hint to choose better query plans.

Unenforced unique constraints are useful on columnstore tables since on a columnstore table, an unenforced unique constraint is a logical-only index - there is no physical storage for the index. SingleStore supports unique constraints on both single-column and multiple-column keys in a columnstore reference table.

Adding or altering a unique index is not supported on existing columnstore tables.

On a rowstore table, an unenforced unique constraint is stored physically as a regular non-unique index.

The RELY and NORELY options specify how the query planner may use the unenforced unique constraint. These options determine whether or not we depend on the index-provided constraint for ensuring correctness in execution.

The NORELY option, which is the default, specifies that query semantics should not rely on the unenforced uniqueness constraint - queries will always return correct results regardless of whether the unenforced unique constraint is true or not. SingleStore may use a NORELY unenforced unique constraint to inform statistical estimates. The query planner assumes the number of unique values in a table is equal to the row count of a table.

The RELY option specifies that SingleStore may choose query plans which assume that the unenforced unique constraint is true - queries are only guaranteed to return correct results if the constraint is true, and queries may return incorrect results if there are duplicate rows. You should only use the RELY option if you know the constraint is valid, e.g. if some process in your application enforces the constraint. SingleStore may use a RELY unenforced unique constraint to inform statistical estimates as well as eliminate redundant joins, for example.

UNIQUE KEY (col1, col2) UNENFORCED

UNENFORCED [RELY | NORELY]

CREATE TABLE t (id INT, SORT KEY (id), UNIQUE KEY (id) UNENFORCED RELY);