You are viewing an older version of this section. View current production version.
Unenforced Unique Constraints
Unenforced unique constraints are available in MemSQL 6.0 and later.
You can specify the UNENFORCED
option on a UNIQUE
constraint to disable enforcement of the constraint. This means that MemSQL 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 regular (enforced) unique constraints are not supported on columnstore tables. On a columnstore table, an unenforced unique constraint is a logical-only index - there is no physical storage for the index.
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.
- 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. MemSQL may use aNORELY
unenforced unique constraint to inform statistical estimates. - The
RELY
option specifies that MemSQL 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 theRELY
option if you know the constraint is valid, e.g. if some process in your application enforces the integrity of the constraint. MemSQL may use aRELY
unenforced unique constraint to inform statistical estimates as well as eliminate redundant joins, for example.
Syntax
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]
If neither RELY
nor NORELY
is specified, the default is NORELY
.
The UNENFORCED
clause is an optional index_option clause in the unique index declaration syntax found in CREATE TABLE, ALTER TABLE, or CREATE INDEX.
For example, as part of a CREATE TABLE
statement:
CREATE TABLE t (id INT, KEY USING CLUSTERED COLUMNSTORE (id), UNIQUE KEY (id) UNENFORCED RELY);
Remarks
- Unenforced unique constraints are not supported on columnstore reference tables.
- Adding, removing, or modifying unenforced unique constraints on a columnstore table is not supported.