Re: SERIAL Columns

>From: Diane Taylor <dtaylor@octa.net> >Date: Thu, 28 Aug 1997 08:46:00 -0700 >X-Informix-List-Id: <list.16237> > >There is a thread running regarding serial numbers. > >I believe there is a misconception that serial numbers assume >uniqueness. Where this is logical, I do not believe it is correct. >When using serial values you must set a unique index or constraint. For >instance, I could manually add a duplicate number. You are correct. When you create a table with a SERIAL column using a CREATE TABLE statement, you must also add a unique constraint on the SERIAL column, either as part of the CREATE TABLE statement (preferred) or with aseparate ALTER TABLE ADD CONSTRAINT statement or a separate CREATE INDEX statement. Unless you do that, you will be able to insert duplicate values in the SERIAL column - other than zero, of course. The confusion arises because the ISQL and DB-Access Schema Editors automatically create the unique constraint on SERIAL columns, but this is just a kindness on their part -- they generate the SQL to ensure that there is a unique constraint on the SERIAL columns. >Informix assigns a serial value based upon the highest last value. If >you have the values 1, 4, 100 the next value Informix assigns will be >101. By using integer values in a load, I could assign any number. >Again, Informix would pick up it's assignment based on the highest >value. Correct. Further, if you recycle the serial numbers (by inserting a big value such as 2147483646 and enough zeros to reset the counter back to 1), then you can get insertion failures because you have a unique constraint and the serial number you are trying to insert already exists... >I have a programmer that uses serial values heavily because an Informix >techy told him they were faster. Personally, I think they are much more >trouble than any performance you gain. I mean, let's get a little bit >twiddly...... The only advantage of SERIAL over INTEGER is that the value is allocated automatically. Simulating that is a royal pain in the posterior. That's why Oracle deigns to provide sequences as their equivalent of SERIAL. I would never encourage the use of SERIAL for performance reasons; I *do* encourage their use for convenience reasons. They provide a commonly required operation at minimal cost (time of execution, time of programmer). Yours, Jonathan Leffler (johnl@informix.com) #include <disclaimer.h> PS: Warning I do not reply to messages with anti-spam in the return path.