Specifying Unenforced Unique Constraints
On this page
You can specify the
UNENFORCED option on a
UNIQUE constraint to disable enforcement of the constraint.
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.
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.
NORELY options specify how the query planner may use the unenforced unique constraint.
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.
NORELY unenforced unique constraint to inform statistical estimates.
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.
RELY option if you know the constraint is valid, e.
RELY unenforced unique constraint to inform statistical estimates as well as eliminate redundant joins, for example.
To specify a unique constraint as unenforced, add the
UNENFORCED option to the index declaration, for example like:
UNIQUE KEY (col1, col2) UNENFORCED
The syntax for the
UNENFORCED clause is
UNENFORCED [RELY | NORELY]
NORELY is specified, the default is
For example, as part of a
CREATE TABLE statement:
CREATE TABLE t (id INT, SORT KEY (id), UNIQUE KEY (id) UNENFORCED RELY);
Last modified: December 14, 2023