box.space.create_check_constraint()

space_object

Create a check constraint. A check constraint is a requirement that must be met when a tuple is inserted or updated in a space. Check constraints created with space_object:create_check_constraint have the same effect as check constraints created with an SQL CHECK() clause in a CREATE TABLE statement.

Parameters:

• space_object (space_object) — an object reference

• check_constraint_name (string) — name of check constraint, which should conform to the rules for object names

• expression (string) — SQL code of an expression which must return a boolean result

Returns

check constraint object

Return type

check_constraint_object

The space must be formatted with /reference/reference_lua/box_space/format so that the expression can contain field names. The space must be empty. The space must not be a system space.

The expression must return true or false and should be deterministic. The expression may be any SQL (not Lua) expression containing field names, built-in function names, literals, and operators. Not subqueries. If a field name contains lower case characters, it must be enclosed in "double quotes".

Check constraints are checked before the request is performed, at the same time as Lua before_replace triggers. If there is more than one check constraint or before_replace trigger, then they are ordered according to time of creation. (This is a change from the earlier behavior of check constraints, which caused checking before the tuple was formed.)

Check constraints can be dropped with {space_object}.ck_constraint.{check_constraint_name}:drop().

Check constraints can be disabled with {space_object}.ck_constraint.{check_constraint_name}:enable(false) or {check_constraint_object}:enable(false). Check constraints can be enabled with {space_object}.ck_constraint.{check_constraint_name}:enable(true) or {check_constraint_object}:enable(true). By default a check constraint is 'enabled' which means that the check is performed whenever the request is performed, but can be changed to 'disabled' which means that the check is not performed.

During the recovery process, for example when the Tarantool server is starting, the check is not performed unless force_recovery is specified.

Example:

 
box.schema.space.create('t')box.space.t:format({{name = 'f1', type = 'unsigned'},                    {name = 'f2', type = 'string'},                    {name = 'f3', type = 'string'}})box.space.t:create_index('i')box.space.t:create_check_constraint('c1', [["f2" > 'A']])box.space.t:create_check_constraint('c2',                        [["f2"=UPPER("f3") AND NOT "f2" LIKE '__']])-- This insert will fail, constraint c1 expression returns falsebox.space.t:insert{1, 'A', 'A'}-- This insert will fail, constraint c2 expression returns falsebox.space.t:insert{1, 'B', 'c'}-- This insert will succeed, both constraint expressions return truebox.space.t:insert{1, 'B', 'b'}-- This update will fail, constraint c2 expression returns falsebox.space.t:update(1, {{'=', 2, 'xx'}, {'=', 3, 'xx'}})

A list of check constraints is in /reference/reference_lua/box_space/_ck_constraint.