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.
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 SingleStoreDB Cloud 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);
Foreign keys in SingleStoreDB are column(s) in a table that establish a link or relationship with a
UNIQUE KEY of another table.
FOREIGN KEY column(s) have corresponding values in the
PRIMARY KEY column of the referenced table.
Foreign key enforcement is not supported, but
FOREIGN KEY syntax can be supported in SingleStoreDB by setting the
ignore_ engine variable to
FOREIGN KEY [foreign_key_name](col1,..., coln) REFERENCES table_referenced (col_referenced));
-- Referenced Table --CREATE TABLE parent (pid INT PRIMARY KEY, col1 INT);-- Foreign Key Table --CREATE TABLE child (cid INT PRIMARY KEY, pid INT, FOREIGN KEY fkey(pid) REFERENCES parent(pid));
If you are migrating an existing application that already has foreign key syntax onto SingleStoreDB, it can make the process easier to set
Since SingleStoreDB doesn't enforce foreign keys, if your application requires foreign key relationships to be maintained, you may wish to check foreign key relationships in application software.
child table must have a corresponding row in the
You may be able to enforce this entirely on the client side (with no database queries needed) by making sure that all transactions update the database in such a way that every child has a parent.
child table like:
INSERT INTO child VALUES (100, 1);
your application can run a
SELECT statement on the
SELECT count(*) FROM parent WHERE pid = 1;
If the result is not
1, your application can signal an error and not run the
Last modified: September 22, 2023