Faking an identity column

Older versions of OS/400 lack an automatically maintained identity column, to uniquely refer to a given record in a table. Depending on use case, it might be programmatically easier to pick a certain record by a a single value, instead of e. g. a multi-column key.

The most easy way to fake this is to define a column as numeric and derive a new value at insert time.

INSERT INTO mytable (id, field1, field2, field3)
SELECT MAX(id)+1,  'foo', 'bar', 'baz' FROM mytable

Weblinks

• Sequence Objects and Identity Columns, datavail.com