AUTOGENERATE Clause

Syntax

AUTOGENERATE (column[, ...])[FROM {long_const|parameter}]

Components


column	Specify the name of the column in which the automatically generated value should be placed. The column must be of datatype long.
FROM	Overrides the default starting value of zero with the specified numeric constant or value found in the specified parameter at run time.

Usage

This clause can be used when specifying an input stream or input window in a project. It cannot be used when specifying an input stream or input window in a module. When input records do not have a natural primary key, this clause provides a way to specify a column that can be used as the primary key.

You can specify more than one column that will have its value automatically generated. The column names must be unique. At run time all of the specified columns will get the same automatically generated value.

The automatically generated columns must be of type long. When the value exceeds the maximum positive value that the long datatype can hold, the value restarts from the maximum negative value that a long datatype can hold.

By default, the values start at zero and increase by one for each insert record. This can be overridden using the FROM clause to explicitly set a starting value, specified as either a parameter or a long_const.

An input window with an auto generated column may be assigned to a log store; in which case, on a restart, the next insert will get the highest sequence number recovered from the log store plus one as the value in the automatically generated column. When there is data in the log store, the FROM clause is ignored on restart.

The automatically generated column is only incremented on an insert and any value explicitly provided in the automatically generated columns of the input row on an insert is ignored. On an update, delete or upsert, the value in the auto generated column is used as it is provided in the input row. This rule has the potential to produce duplicate rows in a window. For example,

The primary key is an auto generated column.
On the first insert, the primary key is set to 0 because the key column is auto generated and the sequence number starts at 0.
If the next row is an upsert with the primary key set to 1, the server will insert this row into the window because there is no row with the primary key of 1 to update.
When another insert comes in, the server will set the auto incremented key column value to 1 and try to insert the row into the input window.
This will cause a duplicate row in the store of the input window and the server will reject the record.

Therefore, it is recommended that the AUTOGENERATE clause not be used with an upsert opcode, especially when the automatically generated value is a primary key.

Examples

The following code creates an input stream named Trades with a column named TradeId for which the values are automatically generated.

CREATE INPUT STREAM Trades
SCHEMA (TradeId long, Symbol string, Shares integer, Price money(4))
AUTOGENERATE (TradeId);

This example creates an input window named Trades with a primary key column named TradeId for which the values are automatically generated.

CREATE INPUT WINDOW Trades
SCHEMA (TradeId long, Symbol string, Shares integer, Price money(4))
PRIMARY KEY (TradeId)
AUTOGENERATE (TradeId);