title: MSSQL Constraint may cause cycles or multiple cascade paths
body: <p>I have a scenario like this:</p>
  
  <p><code>User</code> can own multiple <code>Accounts</code>. <code>User</code> also has bitcoin addresses, (which he inputs by himself) and they are "withdrawal addresses". Each <code>Account</code> also may have multiple bitcoin addresses (which are "deposit addresses").</p>
  
  <p>All addresses are in one table, the only difference is that deposit/withdrawal are just specified by a column <code>Type</code> in table <code>BitcoinAddresses</code>. </p>
  
  <p>I would like to create a scenario where deleting <code>User</code> will cause all the withdrawal <code>BitcoinAddresses</code> he owns to be deleted, and also all the <code>Accounts</code> he owns to be deleted. But deleting an <code>Account</code> should cause the <code>BitcoinAddresses</code> reference to be set to <code>NULL</code>.</p>
  
  <p>I've tried something like that:</p>
  
  <pre><code>CREATE TABLE [dbo].[Users] (
      [Id]                 NVARCHAR (128) NOT NULL,
      [UserName]           NVARCHAR (64)  NULL,
      CONSTRAINT [PK_dbo.Users] PRIMARY KEY CLUSTERED ([Id] ASC),
  );
  
  CREATE TABLE [dbo].[Accounts] (
      [Id]       BIGINT         IDENTITY (1, 1) NOT NULL,
      [UserId]   NVARCHAR (128) NOT NULL,
      [Number] BIGINT         NOT NULL,
      CONSTRAINT [PK_dbo.Accounts] PRIMARY KEY CLUSTERED ([Id] ASC),
      CONSTRAINT [FK_dbo.Accounts.Users_UserId] FOREIGN KEY ([UserId]) REFERENCES [dbo].[Users] ([Id]) ON DELETE CASCADE
  );
  
  CREATE TABLE [dbo].[BitcoinAddresses] (
      [BitcoinAddressId] INT            IDENTITY (1, 1) NOT NULL,
      [Address]          NVARCHAR (MAX) NOT NULL,
      [AccountId]     BIGINT         NULL,
      [UserId]           NVARCHAR (128) NULL,
      [Type]            NVARCHAR (MAX) NULL,
      CONSTRAINT [PK_dbo.BitcoinAddresses] PRIMARY KEY CLUSTERED ([BitcoinAddressId] ASC),
      CONSTRAINT [FK_dbo.BitcoinAddresses_dbo.Accounts_AccountId] FOREIGN KEY ([AccountId]) REFERENCES [dbo].[Accounts] ([Id]) ON DELETE SET NULL,
      CONSTRAINT [FK_dbo.BitcoinAddresses_dbo.Users_UserId] FOREIGN KEY ([UserId]) REFERENCES [dbo].[Users] ([Id]) ON DELETE CASCADE
  );
  </code></pre>
  
  <p>This solution causes an error: </p>
  
  <pre><code>Introducing FOREIGN KEY constraint 'FK_dbo.BitcoinAddresses_dbo.Users_UserId' on table 'BitcoinAddresses' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.
  </code></pre>
  
  <p>But obviously I don't see any cycles in this approach. Can you explain to me why this behaviour happens? How can I solve it? I would like to note, that I don't want to split Wihtdrawal and Deposit Addresses to two different tables (as this would be a correct solution for this scenario, but I want to know why I can't create such references)</p>
  
  <p>Here's my fiddle to play with: <a href="http://sqlfiddle.com/#!6/5d9cd" rel="nofollow">http://sqlfiddle.com/#!6/5d9cd</a></p>