Informix

 View Only
  • 1.  AUTO INCREMENT Value

    Posted Thu June 10, 2021 06:22 AM
    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


  • 2.  RE: AUTO INCREMENT Value

    IBM Champion
    Posted Thu June 10, 2021 06:39 AM
    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
    ------------------------------



  • 3.  RE: AUTO INCREMENT Value

    Posted Fri June 11, 2021 02:36 AM
    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
    ------------------------------



  • 4.  RE: AUTO INCREMENT Value

    Posted Fri June 11, 2021 03:33 AM
    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
    ------------------------------



  • 5.  RE: AUTO INCREMENT Value

    IBM Champion
    Posted Fri June 11, 2021 05:52 AM
    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
    ------------------------------