Using sequences
A sequence is a generator of ordered integer values.
As with spaces and indexes, you should specify the sequence name and let Tarantool generate a unique numeric identifier (sequence ID).
As well, you can specify several options when creating a new sequence. The options determine the values that are generated whenever the sequence is used.
Option name | Type and meaning | Default | Examples |
---|---|---|---|
start |
Integer. The value to generate the first time a sequence is used | 1 | start=0 |
min |
Integer. Values smaller than this cannot be generated | 1 | min=-1000 |
max |
Integer. Values larger than this cannot be generated | 9223372036854775807 | max=0 |
cycle |
Boolean. Whether to start again when values cannot be generated | false | cycle=true |
cache |
Integer. The number of values to store in a cache | 0 | cache=0 |
step |
Integer. What to add to the previous generated value, when generating a new value | 1 | step=-1 |
if_not_exists |
Boolean. If this is true and a sequence with this name exists already, ignore other options and use the existing values | false |
if_not_exists=true |
Once a sequence exists, it can be altered, dropped, reset, forced to generate the next value, or associated with an index.
First, create a sequence:
-- Create a sequence --
box.schema.sequence.create('id_seq',{min=1000, start=1000})
--[[
---
- step: 1
id: 1
min: 1000
cache: 0
uid: 1
cycle: false
name: id_seq
start: 1000
max: 9223372036854775807
...
--]]
The result shows that the new sequence has all default values,
except for the two that were specified, min
and start
.
Get the next value from the sequence by calling the next()
function:
-- Get the next item --
box.sequence.id_seq:next()
--[[
---
- 1000
...
--]]
The result is the same as the start value. The next call increases the value by one (the default sequence step).
Create a space and specify that its primary key should be generated from the sequence:
-- Create a space --
box.schema.space.create('customers')
-- Create an index that uses the sequence --
box.space.customers:create_index('primary',{ sequence = 'id_seq' })
--[[
---
- parts:
- type: unsigned
is_nullable: false
fieldno: 1
sequence_id: 1
id: 0
space_id: 513
unique: true
hint: true
type: TREE
name: primary
sequence_fieldno: 1
...
--]]
Insert a tuple without specifying a value for the primary key:
-- Insert a tuple without the primary key value --
box.space.customers:insert{ nil, 'Adams' }
--[[
---
- [1001, 'Adams']
...
--]]
The result is a new tuple where the first field is assigned the next value from the sequence. This arrangement, where the system automatically generates the values for a primary key, is sometimes called «auto-incrementing» or «identity».
For syntax and implementation details, see the reference for box.schema.sequence.