更新复合主键
我正在努力进行有关是否在 SQL Server 数据库上使用复合主键的哲学讨论。我过去一直使用代理键，并且我正在通过离开舒适区尝试不同的东西来挑战自己。我已经阅读了很多讨论，但还无法得出任何解决方案。我遇到的困难是当我必须用复合 PK 更新记录时。
例如，问题中的记录是这样的：
ContactID, RoleID, EffectiveDate, TerminationDT
本例中的 PK 是(ContactID, RoleID, EffectDate)。 TerminationDT 可以为空。
如果在我的 UI 中，用户更改了 RoleID，然后我需要更新记录。使用代理键，我可以执行Update Table Set RoleID = 1 WHERE surrogateID = Z。但是，使用复合键方式，一旦复合键中的字段之一发生更改，我就无法引用旧记录来更新它，而无需在 UI 中的某处维护对旧值的引用。
我不在 UI 中绑定数据源。我打开一个连接，获取数据并将其存储在存储桶中，然后关闭连接。大家都有什么看法呢？谢谢。
I am struggling with the philosophical discussions about whether or not to use composite primary keys on my SQL Server database. I have always used the surrogate keys in the past and I am challenging myself by leaving my comfort zone to try something different. I have read many discussion but can't come to any kind of solution yet. The struggle I am having is when I have to update a record with the composite PK.
For example, the record in questions is like this:
ContactID, RoleID, EffectiveDate, TerminationDT
The PK in this case is the (ContactID, RoleID, EffectiveDate). TerminationDT can be null.
If in my UI, the user changes the RoleID and then I need to update the record. Using the surrogate key I can do an Update Table Set RoleID = 1 WHERE surrogateID = Z. However, using the Composite Key way, once one of the fields in the composite key changes I have no way to reference the old record to update it without now maintaining somewhere in the UI a reference to the old values.
I do not bind datasources in my UI. I open a connection, get the data and store it in a bucket, then close the connection. What are everyone's opinions? Thanks.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
您通过引用“旧记录”之类的想法来表达的是，该关系具有独立于复合关键元素的语义含义。您需要一个键来引用旧记录这一事实意味着复合键元素是不够的。当然，您仍然保留外键，但对我来说，很明显您在这里需要一个主键，并且它不是通常意义上的代理键。
在我看来，使用代理键是没有罪恶感的。
What you're saying by referencing ideas like "the old record" is that the relationship has a semantic meaning independent of the composite key elements. The very fact that you NEED a key to refer to the old record means that the composite key elements are not sufficient. You still maintain foreign keys, of course, but it seems pretty clear to me that you need a primary key here, and it's not a surrogate key in the usual sense.
Use the surrogate key guilt-free, in my opinion.
您走在正确的道路上，但是查看您的主键，我建议您查看 标准化。我认为您应该尽量避免使用复合键，并尝试每个表使用一个主键，除非有必要。例如，您可以采用 RoleID，使其成为自己的表，其中包含角色描述以及定义角色的其他内容，并将其作为外键引用放在其他表中。
You're on the right track, but looking at your primary keys, I suggest you look into normalization. I think you should try to stay away from using composite keys, and try using one primary key per table, unless necessary. You could take the RoleID, for example, and make it it's own table, with role descriptions, and whatever else defines a role, and put it as a foreign key reference in your other tables.
首先是简单的部分：
看起来您的表格是交叉引用，不是吗？在这种情况下，我发现复合键工作得非常好，如果，这是一个很大的如果，如果你的 UI 和其他库理解它们。如今许多用户界面都没有，听起来你的也没有。因此：
1）您使用复合键进行交叉引用是正确的，这是少数几个始终有意义的地方之一，但是：
2）如果可以的话，您也可以在其中放置代理键其余编码更容易，但请记住在这三列上添加唯一约束。
更进一步，您可能需要一个检查约束来确保日期范围没有重叠，因为数据库本身不支持“范围主键”，而这正是您正在做的事情。
话虽如此，让我进一步混淆事情。为什么要更新这一行？正确的操作不是在原始数据上设置终止日期并强制创建一个新行来指示联系人的新角色吗？在这种情况下，您可能需要保持复合并稍微调整 UI 以允许/禁止某些操作。
First the easy part:
It looks like your table is a cross-reference, no? In this case I have found composite keys to work extremely well, IF, and this is a big IF, If your UI and other libraries understand them. Many UIs these days do not, and it sounds like yours does not. Therefore:
1) You are correct to use a composite key for a cross-reference, that is one of the few places where it always makes sense, but:
2) You can just as well put a surrogate key in there if it makes things easier for the rest of the coding, but remember to add a unique constraint on those three columns.
Going further, you probably need a check constraint that makes sure there are no overlaps in the date ranges, since databases do not natively support "ranged primary keys" which is what it looks like you are doing.
Now with that being said, let me confuse things further. Why are you updating this row? Wouldn't the correct operation be to put a termination date on the original and force creation of a new row indicating the contact's new role? In which case you may get to keep the composite and monkey up the UI a bit to allow/disallow certain actions.