使用GUID和整数在数据库中维护键之间的关系

Question

我正在尝试编写一个应用程序，它将从SQL Server数据库中提取信息，通过Web服务加载对象模型，然后将该信息传递到一个基于不同模式的Access DB中，但其中包含的信息是相同的（因此我认为这类似于ETL过程）。我遇到的问题是，我从SQL Server拉取信息的主键是GUIDs，而我将它们放入的数据库（Access）是INTs。例如：

表1，SQL Server中的Persons表：

╔══════════════════════════════════════╦══════════╦════════════╗
║                  ID                  ║   Name   ║ CreatedOn  ║
╠══════════════════════════════════════╬══════════╬════════════╣
║ 3648F6BB-F743-4952-9C69-93336667F3B1 ║ Person 1 ║ 01/01/2012 ║
║ DE44577A-CAE7-4101-B962-C052214E723B ║ Person 2 ║ 02/01/2012 ║
║ 94115C5E-9C7E-40CF-8A87-D4D837BD5DB3 ║ Person 3 ║ 03/01/2012 ║
║ F93E77D9-C344-420C-9499-BA3B4DD0F0BA ║ Person 4 ║ 04/01/2012 ║
╚══════════════════════════════════════╩══════════╩════════════╝

在Access中的表2，人员表：

╔════╦══════════╦════════════╗
║ ID ║   Name   ║ CreatedOn  ║
╠════╬══════════╬════════════╣
║  1 ║ Person 1 ║ 01/01/2012 ║
║  2 ║ Person 2 ║ 02/01/2012 ║
║  3 ║ Person 3 ║ 03/01/2012 ║
║  4 ║ Person 4 ║ 04/01/2012 ║
╚════╩══════════╩════════════╝

表1是SQL Server数据库中返回的数据，表2是Access数据库中应该显示的信息。所有GUID都应该是整数，但数据之间的关系应保持不变。例如，如果我在SQL Server和Access中运行查询以获取人员的地址（地址表也应类似设置），则无论使用GUID还是整数，查询都应返回相同的结果。

我的想法是在SQL Server中使用ROW_NUMBER()，按CreatedOn日期排序（这是数据库中的日期时间类型，因此应在所有记录中唯一）：

SELECT 
  (ROW_NUMBER() OVER (ORDER BY CreatedOn)) AS ID,
  Name,
  CreatedOn
FROM Table2;

唯一的问题是我发现查询返回了重复的整型ID。例如，上面的表格1会变成这样：

╔════╦══════════╦════════════╗
║ ID ║   Name   ║ CreatedOn  ║
╠════╬══════════╬════════════╣
║  1 ║ Person 1 ║ 01/01/2012 ║
║  2 ║ Person 2 ║ 02/01/2012 ║
║  1 ║ Person 3 ║ 03/01/2012 ║
║  1 ║ Person 4 ║ 04/01/2012 ║
╚════╩══════════╩════════════╝

每个ID都应该是唯一的。有没有人能想出一个好的方法来完成我正在尝试的事情？我目前的做法是否有问题？

任何帮助都将不胜感激。

- rc6886

Answer 1

如果我是你，我不会依赖ROW_NUMBER()，因为你可能无法确定顺序始终相同：

首先，在您的示例中，您仅显示CreatedOn列的日期值（不带时间）。如果有多个人在同一天创建，则无法确定按该日期排序时哪个人先到来。
即使您实际上也在CreatedOn列中有一个时间 - 如果从表中删除一个人，则所有后续人员的ROW_NUMBER将更改。

---

最简单的解决方案是更改其中一个表格，就像webturner在他的答案中建议的那样。
如果由于任何原因（例如您根本不允许更改任何一个数据库的模式，或者如果更改表格的模式会破坏旧有的东西），您无法执行此操作，则可以创建一个映射表，在其中存储两个表之间的关系：

╔══════════════════════════════════════╦══════════╗
║             SqlServerID              ║ AccessID ║
╠══════════════════════════════════════╬══════════╣
║ 3648F6BB-F743-4952-9C69-93336667F3B1 ║    1     ║
║ DE44577A-CAE7-4101-B962-C052214E723B ║    2     ║
║ 94115C5E-9C7E-40CF-8A87-D4D837BD5DB3 ║    3     ║
║ F93E77D9-C344-420C-9499-BA3B4DD0F0BA ║    4     ║
╚══════════════════════════════════════╩══════════╝

如果您无法更改现有数据库，甚至可以将其放入第三个数据库中。

- Christian Specht

Answer 2

最简单的解决方案是在两端之一更改模式，使它们相同。如果您在Access和SQL两端都添加记录，则可以使用GUID来防止为两个不同的记录添加相同的ID。然后，您只需自己创建一个“复制”系统。Access和SQL都支持GUID。

如果无法实现上述解决方案，则需要为每个表格进行某种查找。这将为每个GUID提供整数等效值，反之亦然。如果在Access表（表2）中添加GUID作为附加列，则可以将其用作查找表。

ROW_NUMBER()将返回唯一的数字，但每次使用时都会从1开始，因此每个表格的插入操作都需要在一个集合中完成。如果在Access中使用自动编号字段，这将为您提供跨不同插入的唯一值。

既然您现在在SQL中有GUID和Access表中的唯一ID，您只需在插入具有外键的表格（例如地址表）时进行查找即可。因此，当您从SQL插入带有GUID的地址以识别人员时，请将Access的Person表与GUID进行连接，并插入Person的整数ID。

- Stephen Turner