Serial field issue

auto-incrementdatabase-designinformixprimary-key

Q:

I use a serial as a datatype for my primary keys in my tables.
I insert a lot of data then truncate these data one after one many times to test the data entry.

My question is:

  • Is there any method to initiate the serial again to zero .rather than
    starting from the last truncate index ?without dropping the field and
    create it again.

  • What will happen if the serial reaches to the maximum value?

    related to this question :Auto increment feature in Database

Best Answer

In Informix, a SERIAL column is a 4-byte signed integer with some auto-increment properties.

If you insert the value 0 into a SERIAL column, or fail to provide a value for the serial column, the next number in ascending order will automatically be assigned for this row. In a programming language such as ESQL/C, you can retrieve the value inserted from the SQLCA record. There are also, I think, functions to retrieve the last serial value inserted. If you insert a value into a SERIAL column, that is the value that will be use. Clearly, if there is a unique constraint on the column (there isn't one automatically, though the DB-Access Schema Editor will add one for you) and you insert a duplicate record, then the insertion will fail. If there is no unique constraint, it will succeed. If the newly inserted value, N, is larger than any previously inserted, then the internal counter will be incremented so that the next row inserted with 0 will be assigned N+1.

The second part of the question asks:

  • What will happen when the SERIAL value reaches the maximum?

    • The answer is that it wraps around to 1 again. The new values will be inserted, and as long as the new value does not collide with a value already in the table, all will be well.
    • Note that negative and zero values are all skipped. You can insert negative values explicitly. AFAIK, you cannot insert a value of zero into a SERIAL column1.

This leads to an answer for the first part of the question:

  • Is there any method to initialize the serial again to zero?

    • There is. You insert 231-1 into the serial column. The next row you insert will be assigned the value 1 (not zero). It will keep going from there. Note, however, that if there are many values from the first cycle still in the table, this will cause problems; insertions will fail because the value is already in use.

    • Once upon a very long time ago, in versions that are, I believe, safely out of service (we're talking early or mid 1990s here), you had to insert 231-2 and then 2 rows with zeroes. If you inserted 231-1 directly, the system got confused and jammed itself.

1 You can get a row where there is a zero in the SERIAL column, but only through a back-door cheat. You have to have the column as a plain INTEGER column when you insert the row; then you alter the table so the column is a SERIAL column. So, as I said, you can't insert a row with zero in the SERIAL column, but you can find a row with zero in the SERIAL column if someone is devious enough.

Demonstration

SQL commands prefixed with a + and a blank; output from the DBMS without that prefix. Note that this table was created without a unique constraint or primary key on the serial column; this is not what I'd normally do.

+ begin work;
+ create table serial_x(s serial(100) not null);
+ insert into serial_x(s) values(0);
+ insert into serial_x(s) values(0);
+ insert into serial_x(s) values(0);
+ select * from serial_x;
100
101
102
+ delete from serial_x;
+ alter table serial_x modify(s serial(1));
+ insert into serial_x(s) values(0);
+ select * from serial_x;
103
+ alter table serial_x modify(s serial(1000));
+ insert into serial_x(s) values(0);
+ select * from serial_x;
103
1000
+ insert into serial_x values(2147483647);
+ select * from serial_x;
103
1000
2147483647
+ insert into serial_x values(-1);
+ insert into serial_x values(-1);
+ insert into serial_x values(0);
+ select * from serial_x;
103
1000
2147483647
-1
-1
1
+ rollback;

I formally checked that ALTER ... SERIAL(0) produces the same result as ALTER ... SERIAL(1).

Related Question