Re: Serial Fields
jsturm@zenacomp.zenacomp.com (Jeff Sturm) wrote:
>When a value is inserted into a serial column, Informix performs two checks:
>
> * If the column is zero, it is replaced with the next serial value
> and the next serial value is incremented.
>
> * If the column is greater than the next serial value, the value is
> reset to (column + 1), so future serial inserts will get a unique
> value.
>
>Other than that, a serial column behaves exactly like an integer during insert.
>Consequently a simple unload/load of a table with a serial column should
>always preserve integrity of the data.
>
>There are pitfalls though: if you ever delete rows in a table with a
>serial column AND the table gets dropped/recreated (resetting the serial
>counter back to zero), it may begin reusing serial values that have been
>previously assigned. This will create problems if any other tables still
>reference the original serial values.
>--
>Jeff Sturm
>ZenaComp Incorporated
>jsturm@zenacomp.com
Jeff, Some of your response is incorrect. If rows are deleted,
and you unload the table, recreate it, and reload, the existing
serial values remain static and are not changed in order to reuse
the deleted values. If a column is being explicitly inserted with
a value (like during a load), that value will be inserted. Even
duplicate values can be inserted if you don't have a unique index
or constraint on the column. You can verify with a simple test:
create table foo(foo1 serial not null);
insert into foo values (1);
insert into foo values (2);
insert into foo values (3);
insert into foo values (4);
delete from foo where foo1 < 3; (values 1 & 2 are deleted)
unload to /tmp/foo; (values 3 & 4 are unloaded)
drop table foo;
create table foo (foo1 serial not null);
load from /tmp/foo
insert into foo;
select * from foo; (values of 3 & 4 are maintained);
===========================================================================
Michael Reed - Radiix Inc., 3588 Plymouth Rd., Ste 257, Ann Arbor, MI 48105
Radiix email: mreed@radiix.com Radiix voice: 313-677-UNIX
Ford email: mreed@ese721.ta.ford.com Ford voice: 313-323-2965