Prev Up Next
9.8.2.NOT NULL Home 9.8.4.DEFAULT

9.8.3.IDENTITY (Auto Increment)

The IDENTITY keyword causes the columns to be automatically incremental, meaning that each successive insert into the table will automatically assign a value greater than any previous row of the table. These columns are often referred to as "autoincrement columns". The assigned values are normally consecutive integers. There may be gaps in the sequence if some transactions that reserve a number fail to commit.

An autoincrement column may or may not be part of the primary key or of any index.

The identity_value() function returns the identity column value most recently assigned on the current connection. This can be used inside procedures or from clients. See also the ODBC statement option SQL_GETLASTSERIAL.

The set_identity_column() function allows you to change the identity column sequence value.

The IDENTITY keyword supports the SQL 3 optional clause that allows you to specify a starting value and/or step value in the table creation statement. The syntax is:

      IDENTITY (START WITH <N>, INCREMENT BY <Y>)

Example9.16.Using the IDENTITY declaration 

CREATE TABLE DB.DBA.AI (
        AI INTEGER IDENTITY,
        XX VARCHAR,
        PRIMARY KEY (AI)
  )
;

Creates a table with an autoincrement primary key column AI.

set_identity_column("DB"."DBA"."AI", "AI", 11);

will cause the next row to be inserted into AI to have the AI column equal to 11.


Example9.17.Using the IDENTITY (START WITH) declaration 

CREATE TABLE DB.DBA.AI (
        AI INTEGER IDENTITY (START WITH 100),
        XX VARCHAR,
        PRIMARY KEY (AI)
  )
;

Creates a table with an autoincrement primary key column AI, whose first value will be 100 and will auto-increment from there upwards.


[Tip] See Also

Sequence Objects

Prev Up Next
9.8.2.NOT NULL Home 9.8.4.DEFAULT