Skip navigation.

How to avoid primary key collision on multiple sites?

This article provides several comparative methods to avoid primary key conflicts across multiple sites.

1. Add an offset to primary key

Site 1 - create sequence test_seq start with 1 increment by 1;
Site 2 - create sequence test_seq start with 10000000 increment by 1;

Advantages:
a) Primary key datatype remains number

Disadvantages:
a) On site 2,3... - the primary key becomes too large (as size on disk)
b) Can have primary keys conflicts, over the time, if the start value for site 2,3.. is setting initially too low

2. Concatenate a unique identifier to the current primary key (eg: SITE name)

Advantages:
a) Can't have primary key conflicts over the time
b) Simple to add another site
c) Can add unlimited number of site

Disadvantages:
a) Possible current application code changing to deal with this method
b) The primary key datatype becomes varchar2 instead of number

3. Composite primary key

Worst than method 2 because it is necessary to change the current application code.

4. Intercalated sequences (the best method)

Site 1 - create sequence test_seq start with 1 increment by 10;
Site 2 - create sequence test_seq start with 2 increment by 10;

Advantages:
a) Can't have primary key conflicts over the time
b) Simple to add another site
c) No current application code changing
d) Primary key datatype remains number with small size

Disadvantages:
a) Can add maximum 10 sites (the value of increment by)

5. Use SYS_GUID to generate primary key

Advantages:
a) SYS_GUID generates and returns a globally and randomly-dispersed unique identifier

Disadvantages:
a) RAW datatype instead of number (there are some RAW datatype restrictions)
b) Current application code changing

amazing...

Nothing like "design by recipe", ain't it?
Or "cokie cutter" coding...

Has ANYONE stopped to think that the "short-coming"
of option 4,
"a) Can add maximum 10 sites (the value of increment by)",
is solved VERY SIMPLY by changing the increment?

Oh look: if I change the increment to 100, I get 100 sites.
And I keep all the advantages!

Oh look! If I change the increment to 1000, I get 1000 sites!
That ought to be tops, eh?

sheesh...

nice article.

Ariton did a good job here. What is wrong with a little cookie cutter coding anyway (hey I can illiterate!).

For those of use who have this problem, we can present this article as a nice description of the alternatives and then make a recommendation that will have some weight.

Thanks, Kevin.

How to avoid primary key collision on multiple sites?

Ariton,

It is very nice explanation for different usage of primary key.

For option 5 we can convert RAW datatype to number initially itself e.g.
to_number(sys_guid(), 'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX')

So I do not see any disadvantages using sys_guid(), i.e.

a) a number primary key
b) we do not need to do any code changes in the current application

Ashish