AUTO INCREMENT Value
AMIT PATEL on 11.70 asked whether a column can auto-increment when 0 or NULL is inserted. Art Kagel said to use SERIAL, SERIAL8 or BIGSERIAL, which generate the next value when 0 or no value is inserted. For a start value use SERIAL(initial_value) or BIGSERIAL(initial_value); increment defaults to 1 and can be changed with CDR_SERIAL in ONCONFIG. Amit confirmed inserting 0 incremented the value.
Auto-generated by Claude from the posts below — may be imperfect; read the full thread.
Hello All, I'm working on Informix 11.70. I have created an sequence and for incremented value I'm using (Sequence_name.nextval) every time. But is possible in Informix that if I enter '0' or NULL then also field value should increment automatically by 1 or any integer value. Kindly suggest. Thanks Amit Patel ------------------------------ AMIT PATEL ------------------------------ #Informix
Amit: Yes, absolutely. You have to declare the column type to be one of: SERIAL - 4 byte integer compatible auto-incrementing data type. Range: -2^31 to 2^31-1 SERIAL8 - 10 byte INT8 compatible auto-incrementing data type. Note that the range of SERIAL8 is the same as BIGSERIAL which only uses 8 bytes and so is preferred. BIGSERIAL - 8 byte BIGINT compatible auto-incrementing data type. Range: -2^63 to 2^63-1 A table can have at most one of each auto-incrementing type column. If you insert a zero or fail to insert to any of the serial type columns the next sequential value is automatically inserted. Sequences were added to Informix for compatibility with Oracle and are mainly used when the same set of values is needed to be unique across multiple tables in Informix. Serial types are preferred. Art ------------------------------ Art S. Kagel, President and Principal Consultant ASK Database Management Corp. www.askdbmgt.com ------------------------------
Thanks Art, I forgot to write few thing in that. Can We set a default value against that column, like the value should start from 1000 and then auto increment by 1. 1001 , 1002 .... Kindly revert. Thanks Amit Patel ------------------------------ AMIT PATEL ------------------------------
Dear Art, I entered first record with some value like 10000001 and from next time entered value 0 , so it is taking next values like 10000002 , 10000003 .... :) Thanks Amit Patel ------------------------------ AMIT PATEL ------------------------------
Amit: If you caught me in a bad mood I might tell you to RTFM, but: SERIAL( initial_value ) BIGSERIAL( initial_value ) The increment, if needed, defaults to 1, but you can change that (you would normally only do that in a peer-to-peer ER cluster) using the CDR_SERIAL parameter in the ONCONFIG file. Art ------------------------------ Art S. Kagel, President and Principal Consultant ASK Database Management Corp. www.askdbmgt.com ------------------------------