At 10:41 AM 28/05/2009, you wrote:

>Say I have an INVOICE table with a related LINEITEM table. The Lineitems reference the Invoice through a foreign key.
>
>I would like to be able to freely move invoices and their related lineitems from a source DB to a target DB and back again, either as copies or for archiving purposes to reduce the size of the original DB. I would also like the target DB to be able to have data added in some other way, by other people, perhaps at a different site.
>
>However the row keys as generated by Firebird are not globally unique, so it is theoretically possible that a source DB row ID may already exist in the target DB.
>
>How would one normally deal with this problem? Presumably one must always use globally unique keys, but can these be generated in firebird?

Because generators are generated *within a database*, you have to implement your own system for ensuring that sites don't double-dip. A simple way to do this is to assign a range of each affected generator to each site, sufficiently separated so that there will never be overlaps. Hold the ranges and site ids in an internal control table that ordinary users can only read.

When you're ready to implement, do a SET GENERATOR TO <each external site's starting value> at the respective sites, so that no site can generate the same numbers as another. Make sure your ranges are safe from overlapping. Amongst other things (such as Before Insert triggers to check the table for the start and end ranges of the columns in question) you should avoid declaring these shared key columns as 32-bit integers: use BigInt and ensure that your databases are dialect 3.

From Fb 2.1 on you can abandon generators and use the GEN_UUID() function instead.

./heLen