Recently I did a dbexport of our informix database for our client, but when they tried to load that table into their database, there was an error on trying to insert an id of zero into a serial8 column.
Sure enough if you look at the Informix documentation for serial8 it says negative and zero values are not allowed:
"The SERIAL8 data type does not allow a negative, zero, or NULL value."
- Serial8 documentation
I then looked at the table in question locally and found that indeed at had a 0 id in the database where one should not be:
|id (serial8)||name (varchar255)|
How the heck did that happen? Turns out there some unexpected behaviour in how informix enforces non-positive integers.
First in our above example our zero value got into the table via a migration on the primary key.
Originally the primary key was an int8 which allows zero values. We then inserted a 0 value for the id. Then there was a db migration to alter that primary key to a serial8.
The execution of this revision does not complain that a zero value exists in the table.
Furthermore I did some additional investigation into inserting negative and zero values into a table with serial8 column. Here’s what I found:
- If I try to insert 0 into a serial8 column the insert works with no complaint. If I look at the result I see:
id (serial8) name (varchar255) 1 DEFAULT
- It seems to have incremented the 0 value to a 1
- If I try to insert a negative number into a serial8 column the insert also works with no complaint. If I look at the result I see:
id (serial8) name (varchar255) -1 DEFAULT
Despite the documentation it seems that negative and zero are allowed or not very closely guarded. It seems to me that serial8 is more like a int8 with the exception that if you leave the setting of the id column up to informix it will do the right thing.
If you manually insert though then the rules are pretty much like they are for int8.
Either way you need to be careful.