Hi Jacob,

> Is it ok to use INTEGER as a primary key incremented with at generator trigger?
>
> I understand that a generator returns a BIGINT and that using BIGINT as the primary key is common now, but the range of INTEGER is sufficient for my purposes.

If you're absolutely sure that the positive INTEGER range is big enough (and will be for the lifetime of your database!) then it's perfectly fine to use an INTEGER PK. OTOH, the extra 'cost' for a BIGINT is relatively low.

> CREATE TABLE CARS (
> CarID INTEGER NOT NULL,
> Data CHAR(35),
> CONSTRAINT PK_CARS PRIMARY KEY (CarID)
> );
>
> CREATE SEQUENCE CarID_CARS_GEN;
>
> SET GENERATOR CarID_CARS_GEN 1;

It's valid to mix generator and sequence syntax, but it's probably better (and standards-compliant) to stick with sequence:

ALTER SEQUENCE CarID_CARS_GEN RESTART WITH 1

If you do use SET GENERATOR, don't forget 'TO':

SET GENERATOR CarID_CARS_GEN TO 1

BTW, if you set the generator/sequence to 1, the first issued ID will be 2.


Cheers,
Paul Vinkenoog