DB2 Tutorial

  • Home
  • Start Here
  • Basics
  • Views
  • Triggers
  • Indexes
  • Functions
    • Aggregate Functions
    • Date Functions
    • String Functions
    • Window Functions
Home » Db2 Basics » Db2 Identity Column

Db2 Identity Column

Summary: in this tutorial, you will learn how to use DB2 identity column to define an identity column for a table.

Introduction to Db2 identity column

When you create a new table and use the GENERATED AS IDENTITY option for a column, this column will become an identity column.

An identity column contains a unique integer for each row in the table. When you insert a new row into the table, Db2 automatically generates a sequential integer for the identity column. Thus, identity columns are ideal for the primary key columns such as book id (book_id) or publisher id (publisher_id).

The following shows the syntax of declaring an identity column:

column_name data_type GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY [( identity_option) ]

In this syntax:

First, specify the data type for the identity column. The data type can be SMALLINT, INT, and BIGINT.

Second, use either GENERATED ALWAYS or GENERATED BY DEFAULT option.

  • For the GENERATED ALWAYS option, Db2 will always generate a sequential integer for the identity column. Any attempt to insert a value into the identity column with GENERATED ALWAYS option will result in an error.
  • On the other hand, for the GENERATED BY DEFAULT option, DB2 will only generate the sequential integer when you don’t provide the value for the identity column. If you insert a value into the identity column with the GENERATED BY DEFAULT option, Db2 will use your value instead of using the system generated one.

Third, specify the identity column’s options:

( START WITH starting_value INCREMENT BY increment_value [MINVALUE min_value] [MAXVALUE max_value] [CYCLE | NO CYCLE] )

The identity option allows you to specify the starting value in START WITH clause and increment value in the INCREMENT BY.

If the increment value is positive, you will have an ascending sequence like 1, 2, 3, … In case it is negative, then you will have a descending sequence e.g., -1, -2, -3, …

The MINVALUE and MAXVALUE options allow you to specify the minimum and maximum values that Db2 will generate.

The CYCLE or NOCYCLE option determines whether Db2 should restart the values when it has generated all the possible values.

For example, if you use CYCLE option and the sequence is 1, 2, 3, then Db2 will return 1 if it has generated 3. However, if you use the NO CYCLE option, Db2 will raise an error instead.

Notice that a table can have one and only one identity column in a table in Db2.

Db2 identity column examples

Let’s take some examples of using identity columns to get a better understanding.

1) Db2 identity column example

First, create a new table named t1 with the id column as an identity column.

CREATE TABLE t1( id INT GENERATED BY DEFAULT AS IDENTITY (START WITH 10 INCREMENT BY 10), c1 VARCHAR(10), PRIMARY KEY(id) );

The value of the id column will start with 10 and increment by 10.

Second, use the following INSERT statement to insert three rows into the t1 table:

INSERT INTO t1(c1) VALUES ('A'),('B'),('C');

Third, view data from the t1 table using the following SELECT statement:

SELECT * FROM t1;

Here is the output:

ID C1 ----------- ---------- 10 A 20 B 30 C

2) Db2 identity column with CYCLE example

First, create a new table named t2 whose id column is an identity column.

CREATE TABLE t2( id INT GENERATED ALWAYS AS IDENTITY (START WITH -1, INCREMENT BY 1, CYCLE, MINVALUE -1, MAXVALUE 2), c2 VARCHAR(10) );

Second, insert seven rows into the t2 table:

INSERT INTO t2(c2) VALUES('A'),('B'),('C'),('D'),('E'),('F');

Third, query data from the t2 table:

SELECT * FROM t2;

Here is the output:

ID C2 ----------- ---- -1 A 0 B 1 C 2 D -1 E 0 F 1 G

In this example, the id column’s value starts with -1 and has an increment of one.

Because the MAXVALUE is 2 and CYCLE option is specified, the sequence is -1, 0, 1 ,2, -1, 0, 1 …

In this tutorial, you have learned how to use the Db2 identity column to define an identity column for a table.

  • Was this tutorial helpful ?
  • YesNo
Previous Db2 CREATE TABLE
Next Db2 ALTER TABLE ADD COLUMN

Getting Started

  • What is Db2
  • Installing Db2 Database Server
  • Db2 Sample Database
  • Creating a Db2 Sample Database
  • Connecting to a Db2 Database
  • Interacting with Db2 using SQL Developer

Data Manipulation

  • SELECT
  • ORDER BY
  • WHERE
  • SELECT DISTINCT
  • AND
  • OR
  • BETWEEN
  • LIKE
  • IN
  • LIMIT
  • FETCH
  • Join
  • INNER JOIN
  • LEFT JOIN
  • RIGHT JOIN
  • FULL OUTER JOIN
  • Self-Join
  • CROSS JOIN
  • GROUP BY
  • Subquery
  • HAVING
  • UNION
  • INTERSECT
  • EXCEPT
  • Common Table Expression or CTE
  • INSERT
  • INSERT Multiple Rows
  • INSERT INTO SELECT
  • UPDATE
  • DELETE

Managing Database Objects

  • CREATE TABLE
  • Identity Columns
  • ALTER COLUMN
  • ADD COLUMN
  • DROP COLUMN
  • DROP TABLE
  • TRUNCATE TABLE
  • RENAME TABLE

Db2 Constraints

  • Primary Key
  • Foreign Key
  • DEFAULT
  • NOT NULL
  • CHECK
  • UNIQUE

Db2 Data Types

  • Integer
  • Decimal
  • VARCHAR
  • CHAR
  • DATE
  • TIME
  • TIMESTAMP

Useful Functions & Expressions

  • CAST
  • CASE Expression
  • COALESCE

About db2tutorial.com

The db2tutorial.com website provides you with a comprehensive IBM DB2 tutorial with many practical examples and hands-on sessions.

Recent Tutorials

  • Db2 Functions
  • Db2 RANK
  • Db2 ROW_NUMBER
  • Db2 Window Functions
  • What is Db2

Site Links

  • Home
  • About Us
  • Contact Us
  • Privacy Policy
Copyright © © 2021 by www.db2tutorial.com. All Rights Reserved.