title: SQL Server foreign key with several cascading paths
body: <p>I have three tables in SQL Server 2014.</p>
  
  <ul>
  <li>First one is a <code>Product</code> table (productid, ...) </li>
  <li>Second one contains <code>ProductVersions</code> of that product (ProductVersionID, ProductID, ...)</li>
  <li>Third one contains licenses for the products (LicenseID, ProductID)</li>
  </ul>
  
  <p>These tables have foreign keys on the product ID with on delete cascade. </p>
  
  <p>Now I want to add another table mapping the licenses to specific <code>ProductVersions</code>. This can be a n:m relationship, so I create an mapping table <code>LicenseVersion</code> (<code>LicenseID, ProductVersionID</code>)</p>
  
  <p>When I try to add an foreign key to that relations I get an error saying can't add foreign key because there are loops or serveral cascading paths. Use on delete no action or change the foreign key. </p>
  
  <p>I think, I get why this happens (deleting the product will cause the <code>LicenseVersion</code> row to be deleted from both ways in one transaction) but what is the best practice to solve this?</p>
  
  <p>The database should be consistent anytime, so I don't want to solve this in the software application logic. </p>
  
  <p>I could use a trigger (I think) and an foreign key with no action, but is this the best way?</p>
  
  <p><a href="https://i.stack.imgur.com/UHzJz.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/UHzJz.png" alt="database diagram"></a></p>
  
  <pre><code>CREATE TABLE [dbo].[Products]
  (
     [ProductID] [int] IDENTITY(1,1) NOT NULL,
     [ProductName] [nvarchar](255) NOT NULL,
  
     CONSTRAINT [PK_dbo.Products] 
         PRIMARY KEY CLUSTERED([ProductID] ASC)
  )
  
  CREATE TABLE [dbo].[ProductVersions]
  (
     [ProductVersionID] [int] IDENTITY(1,1) NOT NULL,
     [ProductID] [int] NOT NULL,
     [Name] [nvarchar](255) NOT NULL,
  
     CONSTRAINT [PK_dbo.ProductVersions] 
         PRIMARY KEY CLUSTERED ([ProductVersionID] ASC)
  ) 
  
  CREATE TABLE [dbo].[License]
  (
     [LicenseId] [int] IDENTITY(1,1) NOT NULL,
     [LicenseName] [nvarchar](255) NOT NULL,
     [ProductId] [int] NOT NULL,
  
     CONSTRAINT [PK_dbo.License] 
         PRIMARY KEY CLUSTERED ([LicenseId] ASC)
  )
  
  CREATE NONCLUSTERED INDEX [IX_ProductId] 
       ON [dbo].[License] ([ProductId] ASC)
  
  CREATE NONCLUSTERED INDEX [IX_ProductID] 
       ON [dbo].[ProductVersions] ([ProductID] ASC)
  
  ALTER TABLE [dbo].[License] WITH CHECK 
      ADD CONSTRAINT [FK_dbo.License_dbo.Products_ProductId] 
          FOREIGN KEY([ProductId]) REFERENCES [dbo].[Products] ([ProductID])
                  ON UPDATE CASCADE
                  ON DELETE CASCADE
  
  ALTER TABLE [dbo].[License] CHECK CONSTRAINT     [FK_dbo.License_dbo.Products_ProductId]
  
  ALTER TABLE [dbo].[ProductVersions]  WITH CHECK 
      ADD CONSTRAINT [FK_dbo.ProductVersions_dbo.Products_ProductID] 
          FOREIGN KEY([ProductID]) REFERENCES [dbo].[Products] ([ProductID])
                  ON UPDATE CASCADE
                  ON DELETE CASCADE
  
  ALTER TABLE [dbo].[ProductVersions] CHECK CONSTRAINT     [FK_dbo.ProductVersions_dbo.Products_ProductID]
  
  
  --add new table
  CREATE TABLE [dbo].[LicenseVersion]
  (
     [LicenseID] [int] NOT NULL,
     [ProductVersionID] [int] NOT NULL,
  
     CONSTRAINT [PK_LicenseVersion] 
         PRIMARY KEY CLUSTERED ([LicenseID] ASC, [ProductVersionID] ASC)
  ) 
  
  ALTER TABLE [dbo].[LicenseVersion] WITH CHECK 
      ADD CONSTRAINT [FK_LicenseVersion_ProductVersions] 
          FOREIGN KEY([ProductVersionID]) REFERENCES [dbo].[ProductVersions] ([ProductVersionID])
                  ON UPDATE CASCADE
                  ON DELETE CASCADE
  
  ALTER TABLE [dbo].[LicenseVersion] CHECK CONSTRAINT     [FK_LicenseVersion_ProductVersions]
  
  --error here:
  ALTER TABLE [dbo].[LicenseVersion]  WITH CHECK 
      ADD CONSTRAINT [FK_LicenseVersion_Licenses] 
          FOREIGN KEY([LicenseID]) REFERENCES [dbo].[License] ([LicenseID])
                  ON UPDATE CASCADE
                  ON DELETE CASCADE
  
  ALTER TABLE [dbo].[LicenseVersion] CHECK CONSTRAINT [FK_LicenseVersion_Licenses]
  </code></pre>