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.

Options for box.schema.sequence.create()

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.

Associating a sequence 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.

Version:

Using sequences

Options for box.schema.sequence.create()

Associating a sequence with an index