--- In firebird-support@yahoogroups.com, "michael welsch"
<m.welsch@m...> wrote:

> Hi,
>
> are there any objectives against using two generators with

different

> start values for one index field in a table?
>
> I give a simple Example to illustrate what I mean:
>
> RECREATE TABLE ITEM
> (
> ID NUMERIC( 18, 0) NOT NULL,
> LINKED_ITEM NUMERIC( 18, 0),
> CONSTRAINT PK_ITEM PRIMARY KEY (ID)
> CONSTRAINT FK_ITEM_ITEM FOREIGN KEY (LINKED_ITEM) REFERENCES ITEM
> (ID) ON DELETE CASCADE ON UPDATE NO ACTION;
> );
>
> CREATE GENERATOR GEN_ID_LOW;
> SET GENERATOR GEN_ID_LOW TO 0;
>
> CREATE GENERATOR GEN_ID_HIGH;
> SET GENERATOR GEN_ID_HIGH TO 999999999;
>
> When inserting new elements I would then use different generators
> depending on the originator of the data (i.e. vendor = GEN_ID_LOW

or

> cutomer = GEN_ID_HIGH).
> In case of an update of the vendor originated data, it is then easy
> to tell which data must be copied ( ID >999999999) to the new
> database file to merge it with the new vendor data.
>
>
> I appreciate any comment on this.
> Thanks in advance
> Michael

You of course can get collisions.
The fundemental weakness of your proposal is that business data is
mixed in with database management data (primary key) and this will
only lead to tears: trust me I found this the hard way .
If you want to know who posted the data then make a new flag field
that operates like a boolean.
Sine this business data will probably change keep it as an integer, 0
for vendor entry, 1 for cust entry, 2... for anything added later on.
Mick