Constraints¶
Columns can be constrained in three ways:
The values of a constrained column must comply with the constraint.
PRIMARY KEY¶
The primary key constraint combines a unique constraint and a not-null constraint. It also defines the default routing value used for sharding.
Example:
cr> create table my_table1 (
... first_column integer primary key,
... second_column text
... );
CREATE OK, 1 row affected (... sec)
Currently primary keys cannot be auto generated and have to be specified if data is inserted, otherwise an error is returned.
Defining multiple columns with a primary key constraint is also supported:
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 using a alternate syntax:
cr> 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)
NULL¶
Explicitly states that the column can contain NULL values. This is the default.
Example:
cr> create table my_table2 (
... first_column integer primary key,
... second_column text null
... );
CREATE OK, 1 row affected (... sec)
See also
NOT NULL¶
The not null constraint can be used on any table column and it prevents null values from being inserted.
Example:
cr> create table my_table3 (
... first_column integer primary key,
... second_column text not null
... );
CREATE OK, 1 row affected (... sec)
See also
CHECK¶
A check constraint allows you to specify that the values in a certain column must satisfy a boolean expression. This can be used to ensure data integrity. For example, if you have a table to store metrics from sensors and you want to ensure that negative values are rejected:
cr> create table metrics (
... id TEXT PRIMARY KEY,
... weight double CHECK (weight >= 0)
... );
CREATE OK, 1 row affected (... sec)
Note
For further details see CHECK.