Constraints
Columns can be constrained in three ways:
The values of a constrained column must comply with the constraint.
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)
Note
Not all column types can be used as PRIMARY KEY.
For further details see PRIMARY KEY.
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)
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)
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.