Re: Keys larger than 2 Billion

Mark D. Stock wrote: > ... > If you have more than 2.1 Billion rows, then there will be NO unused > values for SERIAL to allocate. > > A SERIAL itself does not force uniqueness, it simply creates a unique > index by default. If you drop that index and create a composite index > with, say, a DATETIME field as Nils suggested, then you can go beyond > the 2.1 Billion mark. If you say that SERIAL does wrap then this is > one solution. > ... The SERIAL iteself does not even create a unique index, at least not in online 7. All it does is provide for a mechanism to automatically increment a value. Try the following: create table serial_killer (x serial); insert into serial_killer values (1); insert into serial_killer values (1); You will see (e.g. with dbschema) that no indexes are created and therefore no practical way of enforcing a unique constraint. Both inserts complete successfully. So, a composite unique constraint consisting of a serial column and a datetime column can easily accomplish a >2billion value scheme. However, I agree that using a single decimal column is more elegant if you're going to reference the key in other tables. ---------------------------------------------------------------------- John H. Frantz Power-4gl: Extending Informix-4gl frantz@centrum.is http://www.strengur.is/~frantz/pow4gl.html