Int Autoincrementing Primary Key and GUID column

I have an existing database in which the majority of the tables have int primary keys (autoincrementing).

We are now in a position in which we need to now use this db as a central store and allow other clients to connect in and sync data (uploading / downloading data)

With the primary key being an autoincrementing key I know there is the problem of primary ke开发者_如何学运维y collision.

So I was thinking I could add a global key to the synced tables - say a GUID.

and then create a custom sync logical looking up this GUID / comparing client

Does this sound like the right idea ? any other suggestions for implementing a sync framework in which very little can be changed on the central database (no primary key changes)

---

You should look at a replication. I don't know if it will help in your case or not. But replication is generally set up with GUIDs.

---

Generally speaking, an auto-incrementing key will not collide; in fact, I'm not sure how to make it happen (since most definitions in SQL include a Unique constraint). You might get duplicate key errors, though. Why are you letting outside vendors dictate what your internal keys are? Accept a completed record from them, and generate the keys yourself. If you're running out of keys, increase the size of the column (to a long). You could generate a new GUID column, yes, but you'd likely still be generating the GUIDs anyways, so what would be the difference?

Often, most tables in a database will have more than one 'unique' key. These are the 'natural' keys - the actual columns that make up a unique set of data; unfortunately, this may end up being the entire width of the table (which is why id columns are used). Also, unless there is some bizarre use case, don't make the server know about the client ids - make the clients know about the server ids, and keep track of it in a separate column (if necessary) on the client. Then can then have their own internal ids that they use before uploading, that have no relation to the keys you give back - and which they never share with the server.

It doesn't really matter what the server id is (int based or a GUID), although I'd recommend sticking with whatever you have. Whenever the client gives you a row to put on the server, give them back the generated id from the server. This prevents clients from attempting to insert already-in-use ids, and related issues (how do you know the id they gave you shouldn't have been generated by some other device?). I would say that clients do not get to dictate internal ids on the server

---

EDIT:

In light of some quick research into replication (in response to HLGEM), and re-reading the original question; I initially took the question as asking about letting clients dictate the internal primary keys (which is still bad), and/or replacing the int primary keys with the GUID. Although your exact needs may not require it, adding an extra GUID column that the client can populate would work. Some recommendations:
1) Treat unique key violations (accidental or malicious) on the GUID column as a business exception, not a system exception. I'd probably recommend telling the client to re-generate the GUID and re-submit (silently). 2) The client never gets to dictate the acutal primary key column values in the database. In fact, if you use a GUID column, the client may never even need to know about them.