Tip

How to define a sequence number column to a table

In this tip I'll explain how to define a sequence number column to a table that gets populated by the system every time a record is added to that table.

This could be achieved by defining an identity column to that table.

The following example will create a table named TableName with one identity column SEQNUM and a character column Column2. SEQNUM will be the primary key for the new table.

SEQNUM values will be 1, 2, 3, 4, . .... ., 999999999999999.

Example:

Create Table TableName (SEQNUM NUMERIC (15, 0) not null generated always as identity (start with 001 increment by 1), column2 char (03) not null with default, primary key (seqnum))

To insert a record:

Insert into QGPL/tablename (column2) values('ABC')

The data in the table after running the insert statement three times: 

 SEQNUM   COLUMN2
     1     ABC  
     2     ABC  
     3     ABC

This was first published in November 2005

Disclaimer: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.

Back to top