AUTOINC Column Constraint

Purpose

Column constraint to auto increment the value of a column

Syntax

AUTOINC [NEXTVALUE <NextValue> [STEP <StepValue>]]

See Also

ALTER TABLE, CONSTRAINTS, CREATE TABLE

Description

A constraint is used to define rules that help to provide data integrity. Column constraints are specific to the column name specified. You must have ALTER privilege on the table. The table will be locked for EXCLUSIVE use during the operation.

The AUTOINC column constraint is used to auto increment the value of a column whenever a new record is inserted. The NEXTVALUE <NextValue> clause may optionally be used to set the column value of the next inserted record to the integer <NextValue>. The optional STEP <StepValue> clause determines the integer amount the value should be incremented each time a new record is inserted. By default, the first record to be inserted has a column value of 1 and for each new record the value increments by 1.

Specifying the AUTOINC column constraint causes the column to be read only.

NOTE: AUTOINC is column based and multiple columns in the same table can have the AUTOINC column constraint set. The Lianja SEQNO() function works on a per table basis.

Example

CREATE TABLE newcust;
  (acc_num INT AUTOINC NEXTVALUE 10 STEP 5, acc_name char(20))
INSERT INTO newcust;
  (acc_name) VALUES ("Smith")
INSERT INTO newcust;
  (acc_name) VALUES ("Jones")
SELECT * FROM newcust