为了防止XY 问题,这是我们试图解决的实际问题:
问题:
不幸的是,我们有一堆查找表是在主键上使用标识列创建的,它是一个int
. 我们希望我们可以简单地删除标识,但是,我们有一些带有指向标识列的外键的大表,我的理解是在这种情况下删除标识很困难。我们后悔身份的原因是因为这些表需要跨多个环境同步,开发人员通过编写脚本向这些表中插入数据,而我们在多个环境中运行这些脚本但不一定总是以相同的顺序,所以我们问开发人员始终:
- 启用标识插入
- 插入具有硬编码整数 ID 的行
- 禁用标识插入
如果每个人都这样做,数据要么保持同步,要么脚本失败,我们可以立即采取纠正措施来解决冲突。但是当然,有时开发者会忘记遵守规则,只是不加标识地插入,不同环境下不同顺序运行的不同脚本的自增导致它们不同步,然后问题就出现了。
一个想法:
我们可以强制开发人员始终指定标识列吗?我不认为有一种方法可以简单地禁用这些表上的标识。如果我们将身份重新播种到一个小数字怎么办?当种子值已经存在时,任何未指定所有列的插入都将失败,并继续失败,直到插入尝试的次数超过现有(连续)行数。但是在一次正确的插入之后,重新为表播种,下一次不正确的插入将再次使用自动增量。所以这个想法的外推是在每次插入后将表重新播种到一个较低的现有数字(可能使用触发器,这感觉很奇怪,但可能有效?),或者按计划,或者每次我们运行开发人员的脚本时.
这是一个合理的想法,和/或是否有更好的解决方案?
旁注:我们确实有一些其他的想法,我认为这些想法超出了这个问题的范围,例如:
- 一个门控签入,它会解析脚本以插入某些表而不指定标识列,如果我们检测到这一点就会失败。
- 将所有这些数据存储在源中,并在部署时更新整个表。(而不是使用运行一次的插入脚本。)
- 不要在所有环境中运行更改这些表的数据脚本,而是使用复制或其他同步机制。
尽管从长远来看,这些其他想法可能会更好,但似乎最容易实现的目标只是重新播种这些表,因此不正确的插入将失败。
与其“将表重新播种到现有的低数字”,然后在每次“正确”执行它的插入后必须重置种子,不如将其设置为最大值(假设通常为正增量值)数据类型支持的值。
这应该会使
IDENTITY
值的自动生成中断,除非有人重新播种它。示例(返回的错误是“将 IDENTITY 转换为数据类型 int 的算术溢出错误。”在两个 catch 块中)
对此的一种变体(取决于数据类型提供了多少备用容量)可能是为错误的插入保留空间并使用检查约束阻止它们。这可以提供信息更丰富的错误消息。
触发
另一种选择是使用 an instead of trigger:
测试:
这个想法的一个缺点是您不能使用明确的标识值零。
我可能更喜欢其中一种重新播种方法,但这是另一种方法。
删除身份
这是一个痛苦,值得直接的语言支持。
在某些涉及大表的情况下,使用众所周知的程序可以减少创伤
SWITCH
。这并不真正适用于您的情况,因为查找表很小而引用表很大。重要的是,重新创建外键需要一段时间并持有Sch-M
锁,阻止两个表上的所有其他活动。尽管如此:
抽象
解决最后一点,当涉及到一点重定向时,更改内容会容易得多。在您的情况下,部署机制与数据库物理设计(身份属性)紧密相关。
如果没有对表的直接数据访问,并且所有数据更改都通过存储过程进行,那么进行此更改会更容易。添加检查或更改存储过程代码通常是微不足道的。
在理想情况下,部署脚本通常应该能够针对任何当前状态运行(和重新运行),使代码和数据库达到相同的最终状态。毫无疑问,你知道这一切。
我不会说那是唾手可得的果实,因为……
...这听起来像是一列疯狂的火车,但没有奥兹的情况很糟糕。
我建议:
修复流程,并培训某些更适合数据库方面的开发人员成为手动脚本和部署的中心来源(至少涉及身份列时)。
使用SQL Data Examiner等数据同步工具轻松自动生成脚本。
无论哪种情况,我都建议您测试将脚本部署到暂存类型的环境,而不是直接进入生产环境。这样,当确实发生错误时,您就有时间和灵活性来纠正它们,这通常不仅仅是涉及手动脚本时的身份值问题。