Re: Keys larger than 2 Billion
John H. Frantz wrote:
>
> Mark D. Stock wrote:
> > ...
> > 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.
This is my point exactly, SERIAL does not enforce uniqueness itself,
it relies on an index. If you create the table through the schema
editor, then the index is enforced. But as you say, if you use SQL
to create it, the index is not enforced.
The way to write SQL these days is to use PRIMARY keys. If you define
the SERIAL column as a PRIMARY KEY, then a unique index IS enforced.
CREATE TABLE serial_killer
(
x serial,
PRIMARY KEY(x)
);
Will create a PRIMARY KEY CONSTRAINT and hence a unique index on x.
> 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.
Maybe we can get Informix to add a DECIMAL SERIAL type, or perhaps
Illustra already has one. ;-)
Hope that helps,
--
Mark.
+-------------------------------------------------------------------------+
|Mark D. Stock - The West Solutions Group http://www.west.co.za |
| |
|mailto:marks@west.co.za +------------------------------------------------+
|Tel: +27 11 803 2151 |If it doesn't work... force it! |
|Fax: +27 11 803 2189 |If it breaks... it needed replacing anyway! |
|Cell: +27 83 250 2325 |Well, that's how I code anyway! |
+------------------------+------------------------------------------------+