Re: Keys larger than 2 Billion

Mike Segel <mikey@segel.com> wrote: :marco greco wrote: :> :> Sorry, guys my fault. :> :> I was honestly convinced that serial uniqueness was enforced even :> without any unique index on the serial column. This false belief :> dates back to ver 1.xx.xx, when no integrity checks were available :> in the create table statement: an acritic read of the manuals :> ("serials are unique sequential integers assigned automatically :> by informix"), coupled with the fact that you had to create the :> unique index by hand, or rely on isql to have it automatically :> created, convinced me of this. Of course, I've n :> :> OK - serial w/o unique index + timestamp is just fine. :> :OK, :Now I am confused. (Yes, that does happen occasionally when I sober :up. ;-) Brightness doesn't allways come cheap, even to the best once. :A serial field by definition creates a unique index on the :serial column. No, as Marco explained above, it does *not*. If you, like me, create tables via a script (how else can one do it and still maintain version history, comments++) there is *no* index created automatically for the serial column. create table customer( custno serial,custname... ) With the above statement only there is *no* index on this table. You have to create it yourself. As it stands custno can contain any value, including null and duplicates. The one and only effect is the well known that if you give custno the value 0 in an insert statement this is replaced by the next number stored somewhere with the data, and of course if you insert a value higher than the current next number the next again (the stored value) is bumped up. : Are you suggesting to create the table, then :either alter the index or drop the index on a serial column? As you see, no need to alter or drop any indexes. Simply create the once you want. There may have been a time (some older version) where indexes where created automagically for serial columns, but the above is at least the behaviour of ver. 7.x :Is that possible? Yes, and it can be utilized to make the combination of a serial and some other column unique. The *only* time I can possibly see this as usefull is if you have more rows than can be represented uniqualy by a serial. Otherwise the serial alone should definitly be unique. Nils.Myklebust@idg.no <- New E-mail adress NM Data AS, P.O.Box 9090 Gronland, N-0133 Oslo, Norway My opinions are those of my company