SQL Server 2016 Cannot add system versioning to relationship table

Question

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

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/.

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

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?

For eg.

• on 6/1 - User1 had role1 and role2, but
• on 6/4 - User1's role changed to role1 and role3

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.

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

Answer 1

It's important to notice that the limitation of using CASCADE on FOREIGN KEY constraints in temporal tables is applicable only to SQL Server 2016. In SQL Server 2017, this limitation doesn't exist anymore.

This is the relevant part from the official documentation:

ON DELETE CASCADE and ON UPDATE CASCADE are not permitted on the current table. In other words, when temporal table is referencing table in the foreign key relationship (corresponding to parent_object_id in sys.foreign_keys) CASCADE options are not allowed. To work around this limitation, use application logic or after triggers to maintain consistency on delete in primary key table (corresponding to referenced_object_id in sys.foreign_keys). If primary key table is temporal and referencing table is non-temporal, there's no such limitation.

> NOTE: This limitation applies to SQL Server 2016 only. CASCADE options are supported in SQL Database and SQL Server 2017 starting from CTP 2.0.

Answer 2

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.