Identity columns

An identity column contains a unique numeric value for each row in the table. Db2 can automatically generate sequential numeric values for this column as rows are inserted into the table. Thus, identity columns are ideal for primary key values, such as employee numbers or product numbers.

Using identity columns as keys

If you define a column with the AS IDENTITY attribute, and with the GENERATED ALWAYS and NO CYCLE attributes, Db2 automatically generates a monotonically increasing or decreasing sequential number for the value of that column when a new row is inserted into the table. However, for Db2 to guarantee that the values of the identity column are unique, you should define a unique index on that column.

You can use identity columns for primary keys that are typically unique sequential numbers, for example, order numbers or employee numbers. By doing so, you can avoid the concurrency problems that can result when an application generates its own unique counter outside the database.

Recommendation: Set the values of the foreign keys in the dependent tables after loading the parent table. If you use an identity column as a parent key in a referential integrity structure, loading data into that structure could be quite complicated. The values for the identity column are not known until the table is loaded because the column is defined as GENERATED ALWAYS.

You might have gaps in identity column values for the following reasons:

If other applications are inserting values into the same identity column
If Db2 terminates abnormally before it assigns all the cached values
If your application rolls back a transaction that inserts identity values

Defining an identity column

You can define an identity column as either GENERATED BY DEFAULT or GENERATED ALWAYS:

If you define the column as GENERATED BY DEFAULT, you can insert a value, and Db2 provides a default value if you do not supply one.
If you define the column as GENERATED ALWAYS, Db2 always generates a value for the column, and you cannot insert data into that column. If you want the values to be unique, you must define the identity column with GENERATED ALWAYS and NO CYCLE and define a unique index on that column.

The values that Db2 generates for an identity column depend on how the column is defined. The START WITH option determines the first value that Db2 generates. The values advance by the INCREMENT BY value in ascending or descending order.

The MINVALUE and MAXVALUE options determine the minimum and maximum values that Db2 generates. However, the The CYCLE or NO CYCLE option determines whether Db2 wraps values when it has generated all values between the START WITH value and MAXVALUE if the values are ascending, or between the START WITH value and MINVALUE if the values are descending. MINVALUE and MAXVALUE do not constrain a START WITH or RESTART WITH value.

Example: Using GENERATED ALWAYS and CYCLE

Suppose that table T1 is defined with GENERATED ALWAYS and CYCLE:

CREATE TABLE T1
  (CHARCOL1 CHAR(1),
   IDENTCOL1 SMALLINT GENERATED ALWAYS AS IDENTITY
     (START WITH -1,
      INCREMENT BY 1,
      CYCLE,
      MINVALUE -3,
      MAXVALUE 3));

Now suppose that you execute the following INSERT statement eight times:

INSERT INTO T1 (CHARCOL1) VALUES ('A');

When Db2 generates values for IDENTCOL1, it starts with -1 and increments by 1 until it reaches the MAXVALUE of 3 on the fifth INSERT. To generate the value for the sixth INSERT, Db2 cycles back to MINVALUE, which is -3. T1 looks like this after the eight INSERT statements are executed:

CHARCOL1  IDENTCOL1
========  =========
A                -1
A                 0
A                 1
A                 2
A                 3
A                -3
A                -2
A                -1

The value of IDENTCOL1 for the eighth INSERT repeats the value of IDENTCOL1 for the first INSERT.

Example: START WITH or RESTART WITH values outside the range for cycling

The MINVALUE and MAXVALUE options do not constrain the START WITH value. That is, the START WITH clause can be used to start the generation of values outside the range that is used for cycles. However, the next generated value after the specified START WITH value is MNVALUE for an ascending identity column or MAXVALUE for a descending identity column. The same is true if you alter the identity column and specify a RESTART WITH value.

Consider T1 from the previous example, and suppose that you alter the table with a statement that specifies the following keywords.

ALTER TABLE T1 
  ALTER COLUMN IDENTCOL1 SET GENERATED ALWAYS RESTART WITH 99;

Now suppose that you execute the following INSERT statement three times:

INSERT INTO T1 (CHARCOL1) VALUES ('B');

When Db2 generates the IDENTCOL1 value, it starts with 99. However, for the next generated value, Db2 again cycles back to MINVALUE, which is -3. T1 looks like this after the three INSERT statements are executed:

CHARCOL1  IDENTCOL1
========  =========
A                -1
A                 0
A                 1
A                 2
A                 3
A                -3
A                -2
A                -1
B                99
B                -3
B                -2

Identity columns as primary keys

The SELECT from INSERT statement enables you to insert a row into a parent table with its primary key defined as a Db2-generated identity column, and retrieve the value of the primary or parent key. You can then use this generated value as a foreign key in a dependent table.

In addition, you can use the IDENTITY_VAL_LOCAL function to return the most recently assigned value for an identity column.

Example: Using SELECT from INSERT

Suppose that an EMPLOYEE table and a DEPARTMENT table are defined in the following way:

CREATE TABLE EMPLOYEE
  (EMPNO      INTEGER GENERATED ALWAYS AS IDENTITY
              PRIMARY KEY NOT NULL,
   NAME       CHAR(30) NOT NULL,
   SALARY     DECIMAL(7,2) NOT NULL,
   WORKDEPT   SMALLINT);

CREATE TABLE DEPARTMENT
  (DEPTNO     SMALLINT NOT NULL PRIMARY KEY,
   DEPTNAME   VARCHAR(30),
   MGRNO      INTEGER NOT NULL,
   CONSTRAINT REF_EMPNO FOREIGN KEY (MGRNO)
     REFERENCES EMPLOYEE (EMPNO) ON DELETE RESTRICT);

ALTER TABLE EMPLOYEE ADD
  CONSTRAINT REF_DEPTNO FOREIGN KEY (WORKDEPT)
     REFERENCES DEPARTMENT (DEPTNO) ON DELETE SET NULL;

When you insert a new employee into the EMPLOYEE table, to retrieve the value for the EMPNO column, you can use the following SELECT from INSERT statement:

EXEC SQL 
  SELECT EMPNO INTO :hv_empno
    FROM FINAL TABLE (INSERT INTO EMPLOYEE (NAME, SALARY, WORKDEPT)
                      VALUES ('New Employee', 75000.00, 11));

The SELECT statement returns the Db2-generated identity value for the EMPNO column in the host variable :hv_empno.

You can then use the value in :hv_empno to update the MGRNO column in the DEPARTMENT table with the new employee as the department manager:

EXEC SQL 
  UPDATE DEPARTMENT 
    SET MGRNO = :hv_empno
    WHERE DEPTNO = 11;