Sign inTry Free

Specifying Unenforced Unique Constraints

On this page

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.

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, SORT KEY (id), UNIQUE KEY (id) UNENFORCED RELY);

Unenforced Foreign Key Constraints

Foreign keys in SingleStore are column(s) in a table that establish a link or relationship with a UNIQUE KEY of another table. It defines the referential integrity between related tables, ensuring the values in the 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 SingleStore by setting the ignore_foreign_key engine variable to ON. The default value for ignore_foreign_keys is OFF.

Syntax

FOREIGN KEY [foreign_key_name](col1,..., coln) REFERENCES table_referenced (col_referenced));

Example

-- 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 SingleStore, it can make the process easier to set ignore_foreign_keys to ON.

Checking Foreign Keys in an Application or Stored Procedure

Since SingleStore 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. The most common use of a foreign key relationship is to ensure that a one-to-many relationship is maintained between two tables. For example, you may have a rule that any row in the child table must have a corresponding row in the parent table.

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. Or, you may wish to check the constraint by running database queries in your application before doing database operations that might violate it. For example, before inserting into the child table like:

INSERT INTO child VALUES (100, 1);

your application can run a SELECT statement on the parent table:

SELECT count(*) FROM parent WHERE pid = 1;

If the result is not 1, your application can signal an error and not run the INSERT statement. Detecting a foreign key violation like this generally indicates an issue with the application logic that needs to be corrected.

Last modified: February 28, 2024

Was this article helpful?

On this page

Was this article helpful?