Constraints

Columns in CrateDB tables can be constrained in the following ways to enforce data integrity and consistency:

• PRIMARY KEY

• NULL

• NOT NULL

• CHECK

The values stored in constrained columns must comply with their respective constraints.

---

PRIMARY KEY

The PRIMARY KEY constraint enforces two important rules:

• The column(s) must contain unique values.

• The column(s) cannot contain NULL values.

Additionally, the primary key defines the default routing column used for sharding data across the cluster.

Example:

cr> CREATE TABLE my_table1 (
    first_column INTEGER PRIMARY KEY,
    second_column TEXT
);
CREATE OK, 1 row affected (... sec)

Primary keys cannot be auto-generated currently. You must specify the primary key values explicitly when inserting data, or else an error will occur.

Composite Primary Keys

You can define a primary key spanning multiple columns by declaring the constraint on each column individually:

cr> CREATE TABLE my_table1pk (
    first_column INTEGER PRIMARY KEY,
    second_column TEXT PRIMARY KEY,
    third_column TEXT
);
CREATE OK, 1 row affected (... sec)

Or by using an alternate syntax to declare the composite primary key as a table-level constraint:

sqlCopierModifiercr> CREATE TABLE my_table1pk1 (
    first_column INTEGER,
    second_column TEXT,
    third_column TEXT,
    PRIMARY KEY (first_column, second_column)
);
CREATE OK, 1 row affected (... sec)

Not all column types are allowed as primary keys. For more information, see the detailed PRIMARY KEY documentation.

---

NULL

Columns are nullable by default, meaning they can store NULL values unless specified otherwise.

You can explicitly state that a column accepts NULL values:

cr> CREATE TABLE my_table2 (
    first_column INTEGER PRIMARY KEY,
    second_column TEXT NULL
);
CREATE OK, 1 row affected (... sec)

---

NOT NULL

The NOT NULL constraint prevents a column from containing NULL values, ensuring that every row has a valid value in that column.

Example:

cr> CREATE TABLE my_table3 (
    first_column INTEGER PRIMARY KEY,
    second_column TEXT NOT NULL
);
CREATE OK, 1 row affected (... sec)

---

CHECK

The CHECK constraint allows you to enforce custom rules on column values using boolean expressions. It ensures that only valid data satisfying the condition is inserted.

For example, if you have a table storing sensor metrics and want to reject negative values for a weight column:

cr> CREATE TABLE metrics (
    id TEXT PRIMARY KEY,
    weight DOUBLE CHECK (weight >= 0)
);
CREATE OK, 1 row affected (... sec)

Note: For more advanced usage, refer to the detailed CHECK documentation.