维护移动客户端和服务器之间的引用完整性
所以我有一个相对简单的系统。 移动客户端在 sqlite 数据库中创建记录，我希望将其同步到远程 SQL 服务器（与其他移动客户端共享）。因此，当我在手机的 sqlite 表中创建新记录时，我会通过 RESTful API 将该更改推送到我的远程服务。我遇到的问题是如何对主键进行排序，以便数据中不会发生冲突（即手机中的记录与手机上的完全不同的记录具有相同的主键）服务器）。 通常的“在客户端上引用记录以及在服务器上引用同一记录的最佳实践是什么？”
So I have a relatively simple system. A mobile client creates records in a sqlite database that I would like to have synced to a remote SQL server (that is shared with other mobile clients). So when I create a new record in the phone's sqlite table, I then push that change to my remote service through a RESTful API. The problem I'm having, is how do I order the primary keys so that there isn't collisions in the data (i.e. a record in the phone has the same primary key as a completely different record on the server). What is the usual "best practice for referencing the record on the client, and for referencing the same record on the server?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
您可以使用 GUID 类型的列作为主键。据我所知，SQL Server 支持 UNIQUEIDENTIFIER 类型 SQLite 支持 GUID 类型（否则手机上的客户端应用程序必须生成 GUID 值）。这应该保证客户端和服务器上的唯一值。
You could use a GUID type of column for the primary key. SQL Server supports the type
UNIQUEIDENTIFIERSQLite supports the typeGUIDas far as i know (otherwise your client app on the phone has to produce a GUID value). This should guarantee unique values on client and server.GUID是一个不错的选择，但意义不大。
如果您可以发送设备 ID + 递增的身份值和/或时间，那就太好了。
这样，通过查看记录，您可以识别它来自哪个设备以及它们发生的顺序。
根据您的应用程序，这可能是非常有用的信息。
GUID is a good choice but not very meaningful.
It would be nice you could send a device ID + an incrementing identity value and/or a time
That way by looking at the records you could identify what device it came from and the sequence they occured.
Depending on your application this could be very useful information.
选项很少，按照我的喜好排列。如果这些不适用于您的情况，请随时在评论中澄清：
标识一个自然键，或者如果不存在，则创建一个唯一标识本地数据属性的人工键。请注意，人工密钥与代理项不同如此处所述 。将此与唯一的设备 ID 结合使用来创建复合主键
使用SQLite ROWID（代理键）和唯一设备 ID 作为复合主键
为每行使用全局唯一标识符，注意记录的潜在陷阱此处
Few options, in order of my preference. Feel free to clarify in comments if these won't work for your situation:
Identify a natural key, or if one doesn't exist create an artificial one that uniquely identifies local data attributes. Note that an artificial key is different from a surrogate as explained here. Use this with a unique device ID to create composite primary key
Use the SQLite ROWID (surrogate key) and a unique device ID as a composite primary key
Use a global unique identifier for each row, beware of potential pitfalls documented here