DB2 selects between identity and sequential Sequence

URL: http://www.ibm.com/developerworks/cn/data/library/techarticles/0302fielding/0302fielding.html

 

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 ID column, you must use
The exact numeric data type. Because the identity attribute generates a value for the column, this is similar
Default
Clause, so you cannot specify
Default
Clause. The ID column is implicitly defined
Not null
.