Search code examples
database-designprimary-keyreplicationguid

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:

  • Use of auto-incremented ints for PKs is just fine 99% of the time
  • EXCEPT if you're doing replication. Then use GUIDs for your PKs.

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

Solution

  • 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.