--- In Firebird-Java@yahoogroups.com, Stefan Mühlemann <smue@k...> wrote:

> phil_hhn wrote:
>
> > Hi, I have a script which I run to create my database. Is there a way
> > (using standard SQL) to specify that a PK is an auto-increment column?
> > (I.e I understand AUTO_INCREMENT is a MySQL extension and

non-standard).

> > Thanks
> >
>
> You can use Generators.
>
> CREATE GENERATOR GENERATOR_FOR_MY_ID;
>
> INSERT INTO MY_TABLE(ID) VALUES(GEN_ID(GENERATOR_FOR_MY_ID, 1));
>
> See also: http://www.fingerbird.de/generatorguide.htm
>
> Stefan

(Sorry to post more 'general' stuff here to this Java NG, but this is
where I started, so the easiest place to follow up ;-) )

Thanks Stefan, this (along with tips from your generatorguide) has
been working well.

I only have one problem - I have a script which creates the database
and all the generators/triggers. But I also have a script of data
(exported from another system), in the form of several reference
values. This runs ok, after the first bits.
However the next thing is that I want to insert multiple rows from
some temporary 'legacy' table into one of these new tables. Since the
first Pk available is 7 (because there are already the 6 reference
rows from the other script of data) and the generator defaults to 0, I
get a Pk violation when I try to run the insert.
Interestingly if I insert a single row (via the SQL editor in
IBexpert) the generator figures out the right Pk to use (and from
there on it is 'in sync' and I can run the other mass insert).

So basically I need to set the initial generator value to something
other than the default 0 (this has to be done for several tables,
which have different numbers of initial rows). I've tried following
the instructions in the generatorguide but I haven't had much luck. :-(

As I see it, I need to either:
1) add the generators to the db AFTER all the initial reference data
is added (and set the initial starting value for each generator), or
2) tell each generator that when it's called the first time, set
itself to the max(pk), or
3) add the generators to the db at creation time (as I currently do)
then modify the existing reference data so that those rows are added
but use the generator for the Pk's.

I think (3) is probably the most 'correct' solution but may be
technically difficult due to the whole environment (won't tell you all
about that here ;-) )

Any comments re. (1) and (2)?

TIA