Alan McDonald wrote:

>> Ivan Setya Darmawan wrote:
>>> Hi,
>>>
>>> Assume I have 2 branch offices. One at Northpole and the other at
>>> Southpole (brrbb). Each of them has a same customer table with fields
>>> like this:
>>>
>>> customers
>>> ----------------------
>>> cust_id VARCHAR(20)(PK)
>>> cust_name VARCHAR(80)
>>>
>>> And now has come consolidation day. I need to merge northpole customer
>>> table and southpole customer table to a single customer table in
>>> headquater database.
>>>
>>> I think, using autoincrement for cust_id is not a choice, coz it could
>>> arise PK constraint error or cust_id replaced with new increment
>>> value. So, I decide using before insert trigger for cust_id for each
>>> table (including customers table at headquarter) like this:
>>>
>>> BEGIN
>>> /* Trigger body */
>>> IF (NEW.CUST_ID IS NULL) THEN
>>> NEW.CUST_ID = RAND();
>>> END
>>>
>>> and I hope merging those table become visible.
>>>
>>> Do I on right track here or anyone has many better ideas?
>>>
>>> TIA
>>> #Ivan Darmawan
>>>
>> Hello,
>>
>> An usual solution to your problem might be that one table PK starts at 1
>> and the other at 1.000.000.000 or whatever number is comfortable to you.
>> Other solution is to have one PK as 'A'||<some_generator_value> and the
>> other as 'B'||<some_generator_value>.
>>
>> HTH
>> Radu
>
> better still...
> generators for PKs increment by 10 or 100 (being the number of server
> stations you might one day have - maybe 10,000 stations! - so increment by
> 10000.
> Your table in northpole has the generator set to 1 and your southpole DB has
> gen set to 2.
> northpoole records are 1, 11, 21, 31, 41, 51 etc
> soutpole records are 2, 12, 22, 32, 42, 52, 62, 72 etc.
> they never clash.
> If you use bigint for PKs and have an expectation of 1000 server stations -
> you do the math... there's still more records than humans on the earth times
> humans on the earth for each and every station.
>
> Alan
>

You are right,I forgot about this solution. We usually use these two I
wrote before since is faster and more visible (at least in the business
logic we use) to track where the record came from.

Radu