Unenforced Unique Constraints

Info

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.

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

Was this article useful?