修复数据库不一致性 - ID字段

Question

我继承了一个（Microsoft？）SQL数据库，原始状态并不完好。其中仍然存在一些非常奇怪的事情，我正在努力修复 - 其中之一是不一致的ID条目。

在账户表中，每个条目都有一个称为accountID的数字，在其他几个表（注释、设备等）中引用它。问题在于这些数字（由于某种随机原因）范围从-100000到+2000000，而只有大约7000个条目。

有没有好的方法可以重新编号，并同时更改其他表中相应的数字？我还可以使用ColdFusion，因此任何与SQL和/或我接受的内容一起工作的内容都可以。

- Davis

---

为什么这是个问题？ID应该怎么样？ - Tom H

Answer 1

对于代理键，它们的意义在于无意义，因此除非您实际上存在数据库完整性问题（例如没有正确定义外键约束）或者您的标识接近其数据类型的最大值，否则我会将它们保持不变，并寻找其他更有影响力的低垂果实。

- Cade Roux

---

+1 同意，除非系统完全崩溃或接近崩溃，否则不要碰那种东西。 - Kev

我不是数据库专家，而且我已经将数据移植到我制作的表中，所以我要做的事情越来越少了。修复这些数字对我正在进行的用户界面开发也有好处。 - Davis

但是“修复”这些数字会给你带来什么好处呢？当你拥有成千上万甚至数百万个代理键时，你不会对它们产生依恋的;-) - Cade Roux

是的，我想我会使用CF来解决困扰我的OCPD的负面因素，然后尝试说服我半技术的老板这是一个好的选择。 - Davis

iuvat，正如Cade所说，不要对那些数字产生依赖，你可以将ID视为C语言中的指针或其他编程语言中的对象引用。值并不重要，重要的是它引用了正确的对象（表中的适当行）。 - kristof

+1 给 Kristof。如果这些值在“商业”意义上没有意义，它们的唯一目的是在数据之间创建关系，那么我会永远不去改变它们。 - Kev

Answer 2

在这种情况下，“为什么”似乎比“如何”更好。原帖提到有一个奇怪的问题需要解决，但没有说为什么这是个问题。它是否引起了问题？更改这些数字会有什么积极影响？除非您最初编写了系统并且准确理解数字处于当前状态的原因，否则像这样进行更改是相当有风险的。

- robsymonds

---

确实。对于标识符来说，非连续的情况很常见，我不认为这是一个问题。 - bortzmeyer

Answer 3

如果这是一个财务应用程序，我建议您与会计师（或至少您的财务人员）交谈，然后再尝试更改账户表中的数字。账户表对财务报告非常关键。这些ID可能有您不理解的含义。除非有原因，否则没有人会输入负ID。除非我知道为什么要将其更改为负数，否则我绝不会更改它。通过进行不必要的更改，您可能会真正搞砸您的税务申报或其他事情。

- HLGEM

---

相信我，我对这个数据库非常了解，因为我已经将所有信息从一个设计不良的数据库移植过来了。此外，我还亲自编写了唯一与其交互的应用程序，所以我很确定将数字改为正数不会有任何影响。这只是由于最初的糟糕程序员导致的问题。 - Davis

Answer 4

如果您可以将其离线，您可能可以禁用外键关系，然后使用脚本更新主键。我以前曾使用过此更新脚本来更改值，您可以很容易地将此代码包装在游标中，逐个查看相关的关键值，并将任意值更新为一个您正在跟踪的递增值。

在这里检查脚本：http://vyaskn.tripod.com/sql_server_search_and_replace.htm

如果您只有使用主键的表清单，您可以设置一系列UPDATE语句，这些语句在您的游标内运行，那么您就不需要使用这个脚本（它可能有点慢）。

值得问一下的是，为什么这些值看起来不匹配。这个数据库是否经常添加和删除值？主键值真的是任意的吗，还是它们只是表面上看起来如此，但实际上它们确实有意义？尽管我支持合并，但您必须确保这些值没有目的。

- rwmnau

---

数据库是以某种方式设计的，我当时不在那里，其中有一些奇怪的事情。曾经有一段时间，一个叫做servicecallID的数字代表日期，而不是递增的数字。它一直跳过到2005xxxx然后又重新开始递增。 - Davis

Answer 5

使用ColdFusion，应该不会是一个艰巨的任务，但会有些混乱，需要小心处理。一种可能的方法是编写脚本以操作数据库并生成全新、空白的表结构。在新数据库中将accountID设置为identity字段。

然后，使用ColdFusion编写一个查询，逐一将所有旧账户数据插入到新数据库中。对于每一行数据，让新数据库分配一个新ID。在每次插入之后，使用@@IDENTITY或MAX（accountID）提取新ID，并将新ID和旧ID一起存储在临时表中，以便知道哪些旧ID属于哪些新ID。

接下来，对每个子表重复此过程。对于每个旧ID，提取其子项并使用新ID重新插入到新数据库中。如果子表的主键没有问题，可以按原样插入，或者如果不重要，可以让服务器分配新主键。

通过暂时禁用关系来直接在原位分配新ID也可能有效，但如果其中一个条目被分配了已经被旧数据使用的ID，则可能会发生冲突。

- Justin Scott

---

我已经为其中一个较小的表格制作了一个 Coldfusion 页面，但是它对于这个表格来说太慢了，而且由于某种原因我还遇到了不匹配的问题。你有适用的模拟代码吗？ - Davis

不要使用@@identity来获取新的id，这可能会导致不正确的结果并破坏数据完整性。请改用scope_identity()。 - HLGEM

Answer 6

在账户表中创建一个新的列来存储你的新ID，并在每个相关表中创建一个新的列来引用这个新的ID列。

ALTER TABLE accounts
ADD new_accountID int IDENTITY

ALTER TABLE notes
ADD new_accountID int

ALTER TABLE equipment
ADD new_accountID int

然后，您可以将每个引用表上的new_accountID列映射到accounts表。

UPDATE notes
SET new_accountID = accounts.new_accountID
FROM accounts
INNER JOIN notes ON (notes.accountID = accounts.accountID)

UPDATE equipment
SET new_accountID = accounts.new_accountID
FROM accounts
INNER JOIN equipment ON (equipment.accountID = accounts.accountID)

此时，每个表都有旧密钥的accountID和新密钥的new_accountID。从这里开始应该很简单。

1. 打破所有关于accountID的外键。
2. 在每个表上，执行UPDATE [table] SET accountID = new_accountID。
3. 重新添加accountID的外键。
4. 从所有表中删除new_accountID，因为它不再需要。

- Patrick McElhaney