Actual operations for auto-increment primary keys in DB2 Databases

The following article describes how to automatically increase the primary key of a DB2 database, in practice, the DB2 database can use generated always as identity to specify a related field as a self-increasing field, for example:

This indicates that id is automatically a self-increasing field, which increases by 1 each time from 1. You can also use generated to calculate the field value through other fields, for example;

 

 
 

  
  
create table user(name varchar(30),   
  
  
n1 integer,   
  
  
n2 integer ,   
  
  
id integer generated always as (n1+n2))  
 
 

For example:

 

 
 

  
  
create table strategy   
  
  
(   
  
  
strategy_id decimal(17)   
  
  
GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1 )   
  
  
primary key not null,   
  
  
strategy_name varchar(200),   
  
  
area_code decimal(6,0)   
  
  
);   
  
  
GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1 )  
 
 

Auto-increment starts from 1, and 1 is added each time.

DB2 Identity field and sequence:

The biggest difference between the Identity field and sequence is that identity can only be used in one table, and identity cannot be shared among multiple tables; sequence can share the serial number generator in multiple tables in the same DB2 database through naming.

Two methods for generating digital sequences

In DB2, two methods can be used to automatically generate a numerical sequence:

Define columns with the IDENTITY attribute.

Create a SEQUENCE object.

IDENTITY column

When you use the IDENTITY attribute to define a table column, each time a row is inserted into the table, a value is automatically generated for the column.

SEQUENCE object

The second method that enables DB2 to automatically generate a digital SEQUENCE is to create a SEQUENCE object. You can use a sequence expression to reference a sequence object. Sequence expressions can appear in most positions where expressions can appear. Sequence expressions can specify whether the value to be returned is a new value or a previously generated value.

If the sequence reference is for the next value, a value is automatically generated for the sequence and returned as the result of the sequence expression. For example, if a sequence named orders_seq has been created, the sequence expression returns the next value generated for the sequence:

 

 
 

  
  
NEXT VALUE FOR orders_seq  
 
 

If the sequence reference is for the previous value (previous value), the value generated for the sequence in the previous SQL statement is returned as the result of the sequence expression. This sequence expression returns the previous value generated by the sequence:

 

 
 

  
  
PREVIOUS VALUE FOR orders_seq 
 
 

Note: When DB2 UDB introduced sequences, it once supported the non-standard SQL syntax NEXTVAL to replace NEXT VALUE, and PREVVAL to replace PREVIOUS VALUE. These variants continue to be supported.

Select between ID and Sequence

Although both identifiers and sequences are used to generate values, you may need to use one instead of the other according to special circumstances.

The identification column is useful in the following situations:

The table has only one column of values to be automatically generated.

Each row must have an independent value.

Use an Automatic Generator to generate the table's primary key

The process of generating a new value is closely related to the insert operation on the table, no matter how the insert operation occurs.

Sequence objects are useful in the following scenarios:

Store the values generated from a sequence to multiple tables

Each table has multiple columns that need to be automatically generated (multiple values may be generated for each row by using the same sequence or multiple sequences)

The process for generating new values has nothing to do with any reference to the table.

Unlike a sequence object, the ID column is defined in a table, so some restrictions are required. Each table can have only one identification column. When creating a column as the identification column, the exact numeric data type must be used for this column. Because the identity attribute generates a value for the column, which is similar to the DEFAULT clause, you cannot specify the DEFAULT clause when defining the identity column. The identifier column is implicitly defined as not null. The above content is an introduction to the actual operation methods for auto-increment primary keys in the DB2 database. I hope you will gain some benefits.