SQL Server 2016 无法将系统版本控制添加到关系表 [英] SQL Server 2016 Cannot add system versioning to relationship table

问题描述

SQL Server 2016 系统版本控制很酷.我正在使用免费的开发人员版本.谢谢女士！

The SQL Server 2016 system versioning is cool. I am using the free Developer version. Thanks MS!

我无法确定它是否会给我多对多关系的版本控制.我有一个包含角色集合的用户对象，反之亦然.实体框架生成了 UserRoles 表，该表保存了 User 和 Roles 之间的关系.我能够使用这篇文章为 User 和 Roles 表打开系统版本控制http://sqlhints.com/tag/modify-existing-table-as-system-versioned-temporal-table/.

I am unable to figure out if it will give me versioning of many to many relationships. I have a User object that has a collection of Roles and vice versa. Entity Framework has generated the UserRoles table that holds the relationship between User and Roles. I was able to turn on system versioning for the User and Roles tables using this article http://sqlhints.com/tag/modify-existing-table-as-system-versioned-temporal-table/.

但是，我无法为 UserRoles 启用.我收到一个错误

But, I am not able to turn on for UserRoles. I get an error

将 SYSTEM_VERSIONING 设置为 ON 失败，因为表有一个带有级联 DELETE 或 UPDATE 的 FOREIGN KEY.

Setting SYSTEM_VERSIONING to ON failed because table has a FOREIGN KEY with cascading DELETE or UPDATE.

这是否意味着我们无法知道多对多关系的版本控制?

Does this mean we cannot know the versioning for many-many relationships?

例如.

• 在 6/1 - 用户 1 有角色 1 和角色 2，但是
• 6 月 4 日 - 用户 1 的角色更改为角色 1 和角色 3

所以，如果我想知道用户在 6/1 的状态，我认为只有在 UserRoles 上打开系统版本控制才能做到这一点，但这不起作用.

So, if I wanted to know the state of the user on 6/1, I thought that's possible only by turning on system versioning on UserRoles, but that's not working.

这是否可行或不受 SQL Server 2016 支持?如果没有，有没有其他方法可以实现?

Is this doable or not supported by SQL Server 2016? If not, is there any other way this can be accomplished?

推荐答案

听起来像是 ON UPDATE CASCADE 或 ON UPDATE DELETE 外键是问题所在.删除级联并将其替换为知道并处理正确关系的删除过程，您应该没问题.

Sounds like it's the ON UPDATE CASCADE or ON UPDATE DELETE foreign key that's the issue. Remove the cascading and replace that with a delete proc that knows and handles the proper relationships and you should be fine.

就我个人而言，我喜欢知道我的删除/更新在做什么，而不是相信关系来处理所有这些.我可以看到潜在的锁定问题，并且知道有时我真的想阻止更新或删除，而不是让它在所有看不见的表中级联.

Personally, I like knowing what my deletes/updates are doing rather than trusting the relationships to handle all of them. I can see potential locking issues as well as know that there are times I really want to prevent an update or delete rather than letting it cascade through all of the tables unseen.

这篇关于SQL Server 2016 无法将系统版本控制添加到关系表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！