OraQA

Oracle Question and Answer

  • Do you have a solution to a problem? Do you have an unanswered question? Login and share it with the Oracle community. More...

Oracle News


Entries RSS feed

Comments RSS feed

How to avoid primary key collision on multiple sites

February 8th, 2007 By Claudiu Ariton

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

Leave a Reply

You must be logged in to post a comment.

RSS feed for comments on this question