Constraints¶

PRIMARY KEY¶

The PRIMARY KEY constraint specifies that a column or columns of a table can contain only unique (non-duplicate), non-null values.

Using columns of type object, geo_point, geo_shape or array as PRIMARY KEY is not supported.

To use a whole object as PRIMARY KEY each column within the object can be declared as PRIMARY KEY instead.

Adding a PRIMARY KEY column is only possible if the table is empty.

INDEX¶

The INDEX constraint specifies a specific index method on one or more columns.

It is possible to define more than one index per table, whether as a column constraint or a table constraint.

For further details about the meaning of the options see Fulltext indices.

CHECK¶

The CHECK constraint specifies that the values of certain columns must satisfy a boolean expression on insert and update.

Syntax:

[CONSTRAINT check_name>] CHECK (boolean_expression)

If CONSTAINT check_name is omitted, CrateDB generates a unique name automatically. This name is visible in table_constraints and can be used to DROP CONSTRAINT a constraint.

The CONSTRAINT definition can either be inline with a column, like this:

cr> CREATE TABLE metrics1 (
...     weight REAL CONSTRAINT weight_is_positive CHECK (weight >= 0),
... );
CREATE OK, 1 row affected  (... sec)

Or, also inline, but without explicit name:

cr> CREATE TABLE metrics2 (
...     weight REAL CHECK (weight >= 0),
... );
CREATE OK, 1 row affected  (... sec)

Or, on a table level with explicit name:

cr> CREATE TABLE metrics3 (
...     weight REAL,
...     CONSTRAINT weight_is_positive CHECK (weight >= 0),
... );
CREATE OK, 1 row affected  (... sec)

Or without name:

cr> CREATE TABLE metrics4 (
...     weight REAL,
...     CHECK (weight >= 0),
... );
CREATE OK, 1 row affected  (... sec)

Multiple columns can be referenced:

cr> CREATE TABLE metrics5 (
...     weight REAL,
...     qty INTEGER,
...     CHECK (weight * qty != 1918),
... );
CREATE OK, 1 row affected  (... sec)

cr> CREATE TABLE metrics6 (
...     weight REAL,
...     qty INTEGER CHECK (qty * weight != 1918)
... );
CREATE OK, 1 row affected  (... sec)

CHECK constaints can be removed DROP CONSTRAINT.

NOT NULL¶

The NOT NULL constraint specifies that a column of a table can contain only non-null values.

The columns that are part of the primary key of a table are NOT NULL by default.