Surmounting Identity Column Challenges

June 11, 2014 Ted Holt

Using an identity column is a wonderful way to ensure that each row of a table (record of a physical file) has a unique identifier. It sure beats storing the next batch (invoice, transaction, etc.) number in a data area or a one-record database file. Here are a few things to keep in mind when inserting rows (writing records) into a table that has an identity column.

1. Let the system assign the value to the identity column. You do this by specifying GENERATED ALWAYS or omitting the GENERATED option when you create the table.

create table Sales
  ( ID                integer
                      generated always as identity,
    Invoice           dec  (9,0),
    Line              dec  (3,0),
    InvoiceDate       date,
    Customer          dec  (7,0),
    Item              char (12),
    Quantity          dec  (3,0),
    Price             dec  (5,2),
  primary key (ID))

Or:

create table Sales
  ( ID                integer as identity,
. . . etc . . .

The other option, GENERATED BY DEFAULT, allows you to assign a value of your choosing to the identity column. There are probably situations that call for this option, but I can’t think of any at the moment.

2. One way to insert a row is to list the columns (fields), omitting the identity column.

insert into Sales
   (Invoice, Line, InvoiceDate, Customer, Item, Quantity, Price)
values (1001, 002, '2014-06-04', 6004, 'AB-221',  6,    5 )

3. If you have a lot of columns in a table (think dozens or even hundreds of columns), you may not want to list them. If you don’t list the columns, assign the value DEFAULT to the identity column.

insert into Sales values
(default, 1001, 002, '2014-06-04', 6004, 'AB-221',  6,    5 )

The system assigns the next value to the ID column.

4. Combining two tables can be a challenge. What if you want to add all the rows in the sales table to the sales history table, and both tables have some of the same values in their identity columns? Don’t panic. Just add OVERRIDING USER VALUE to the statement.

insert into SalesHistory
   overriding user value
   select * from Sales

The system will ignore the values in the ID column of the Sales table and assign new values to the ID column when it inserts the Sales rows into SalesHistory.

I have written plenty of code that retrieves the next sequential number from a data area or physical file, and I wouldn’t think of changing any of it that is still in production. But I don’t plan to use that technique again.