box.space.create_check_constraint()

object space_object¶

space_object:create_check_constraint(check_constraint_name, expression)¶

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
Return:	check constraint object
Rtype:	check_constraint_object

The space must be formatted with space_object: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 false
box.space.t:insert{1, 'A', 'A'}
-- This insert will fail, constraint c2 expression returns false
box.space.t:insert{1, 'B', 'c'}
-- This insert will succeed, both constraint expressions return true
box.space.t:insert{1, 'B', 'b'}
-- This update will fail, constraint c2 expression returns false
box.space.t:update(1, {{'=', 2, 'xx'}, {'=', 3, 'xx'}})

A list of check constraints is in box.space._ck_constraint.