Faking an identity column: Difference between revisions
(new) |
(New) |
||
| Line 7: | Line 7: | ||
SELECT MAX(id)+1, 'foo', 'bar', 'baz' FROM mytable | SELECT MAX(id)+1, 'foo', 'bar', 'baz' FROM mytable | ||
</syntaxhighlight> | </syntaxhighlight> | ||
There is a certain chance that concurrently active processes derive the same ID and those <tt>INSERT</tt>s might fail with a duplicate key error. As long as this ID is not referenced by other tables as a primary key value, the easiest way to counter this is to programmatically try to insert the record in a loop<ref>You should ensure that only a duplicate key error forces another iteration. Other errors can happen.</ref> until the <tt>INSERT</tt> eventually succeeds. Similar ways can be done with classic positional API calls, and a LF defined only for the ID column.<ref>An example can be found in the [https://github.com/PoC-dev/as400-sfltemplates/blob/main/v_lodallpg.rpgle v_lodallpg.rpgle] I wrote. Look for subroutine <code>INCLASTID</code>.</ref> | |||
Other ways of obtaining IDs are pointed out briefly in ''Sequence Objects and Identity Columns''. | |||
== Weblinks == | == Weblinks == | ||
* [https://www.datavail.com/blog/sequence-objects-identity-columns/ Sequence Objects and Identity Columns], datavail.com | * [https://www.datavail.com/blog/sequence-objects-identity-columns/ Sequence Objects and Identity Columns], datavail.com | ||
* [https://github.com/PoC-dev/as400-sfltemplates Subfile Templates], GitHub | |||
== Footnotes == | |||
<references /> | |||
[[Category: SQL]] | [[Category: SQL]] | ||
Revision as of 19:09, 20 April 2025
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
There is a certain chance that concurrently active processes derive the same ID and those INSERTs might fail with a duplicate key error. As long as this ID is not referenced by other tables as a primary key value, the easiest way to counter this is to programmatically try to insert the record in a loop[1] until the INSERT eventually succeeds. Similar ways can be done with classic positional API calls, and a LF defined only for the ID column.[2]
Other ways of obtaining IDs are pointed out briefly in Sequence Objects and Identity Columns.
Weblinks
- Sequence Objects and Identity Columns, datavail.com
- Subfile Templates, GitHub
Footnotes
- ↑ You should ensure that only a duplicate key error forces another iteration. Other errors can happen.
- ↑ An example can be found in the v_lodallpg.rpgle I wrote. Look for subroutine
INCLASTID.