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
------------------------------

Original Message:
Sent: Thu June 10, 2021 06:38 AM
From: Art Kagel
Subject: AUTO INCREMENT Value

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
------------------------------

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
------------------------------

Original Message:
Sent: Fri June 11, 2021 02:35 AM
From: AMIT PATEL
Subject: AUTO INCREMENT Value

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
------------------------------


Original Message:
Sent: Thu June 10, 2021 06:38 AM
From: Art Kagel
Subject: AUTO INCREMENT Value

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

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
------------------------------

Original Message:
Sent: Fri June 11, 2021 02:35 AM
From: AMIT PATEL
Subject: AUTO INCREMENT Value

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
------------------------------


Original Message:
Sent: Thu June 10, 2021 06:38 AM
From: Art Kagel
Subject: AUTO INCREMENT Value

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