title: SQL Server 2016 Cannot add system versioning to relationship table
body: <p>The SQL Server 2016 system versioning is cool. I am using the free Developer version. Thanks MS!</p>
  
  <p>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 <code>UserRoles</code> table that holds the relationship between <code>User</code> and <code>Roles</code>. I was able to turn on system versioning for the <code>User</code> and <code>Roles</code> tables using this article <a href="http://sqlhints.com/tag/modify-existing-table-as-system-versioned-temporal-table/" rel="noreferrer">http://sqlhints.com/tag/modify-existing-table-as-system-versioned-temporal-table/</a>.</p>
  
  <p>But, I am not able to turn on for <code>UserRoles</code>. I get an error </p>
  
  <blockquote>
    <p>Setting SYSTEM_VERSIONING to ON failed because table has a FOREIGN KEY with cascading DELETE or UPDATE.</p>
  </blockquote>
  
  <p>Does this mean we cannot know the versioning for many-many relationships?</p>
  
  <p>For eg. </p>
  
  <ul>
  <li>on 6/1 - User1 had role1 and role2, but </li>
  <li>on 6/4 - User1's role changed to role1 and role3</li>
  </ul>
  
  <p>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 <code>UserRoles</code>, but that's not working. </p>
  
  <p>Is this doable or not supported by SQL Server 2016? If not, is there any other way this can be accomplished?</p>