CODEWITHSUNDEEP
database-designprimary-keyreplicationguid
theog
theog

Reputation: 1064

Why is a GUID is the best choice for a PK if you're going to be doing replication?

I'm building an intranet web app with an Oracle back end. The Oracle DB will be replicated on another server for reasons that aren't important to this discussion. I'm reasonably certain we'll be using Oracle Basic Replication, not Advanced.

Can someone explain to me why most discussions around Primary Keys go something like:

Nobody ever seems to explain why the exception for replication, and I don't understand why.

Upvotes: 3

Views: 1205

Answers (3)

kevin42
kevin42

Reputation: 2128

If you consider what happens when two servers that are replicated with each other and have data being added to them between synchronization, the problem becomes apparent:

Time  Server  NewID
001   A       1
002   A       2
003 <sync of server A and B>
004   A       3
005   B       3
006 <sync of server A and B>

<---two different records have the same ID now, so there's a conflict!

Upvotes: 1

ChrisLively
ChrisLively

Reputation: 88064

One word: MERGE

The only way merge replication can work is if all of the "master" boxes can create a new key without it conflicting with the others.

Upvotes: 3

Tom Ritter
Tom Ritter

Reputation: 101340

I believe the GUID-for-replication argument only realy applies if both servers will be receiving new data. Then you don't want new rows to conflict, so use GUIDs as the PK - replication can go both ways.

We replicate autoincrement tables all the time, but it's a strict master/slave replication where data is only written to the Master.

Upvotes: 7

Related Questions