3

I have a scenario like this:

User can own multiple Accounts. User also has bitcoin addresses, (which he inputs by himself) and they are "withdrawal addresses". Each Account also may have multiple bitcoin addresses (which are "deposit addresses").

All addresses are in one table, the only difference is that deposit/withdrawal are just specified by a column Type in table BitcoinAddresses.

I would like to create a scenario where deleting User will cause all the withdrawal BitcoinAddresses he owns to be deleted, and also all the Accounts he owns to be deleted. But deleting an Account should cause the BitcoinAddresses reference to be set to NULL.

I've tried something like that:

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
);

This solution causes an error: 

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.

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)

Here's my fiddle to play with: http://sqlfiddle.com/#!6/5d9cd

Kelu Thatsall
  • 2,494
  • 1
  • 22
  • 50
  • Your design has one more problem. A row in `BitcoinAddresses` can reference a `User` and an `Account` with different `UserId`s. Is this intentional? If not, you can remove the 2 FKs and use only one, with: `FOREIGN KEY (UserId, AccountId) REFERENCES [dbo].[Accounts] (UserId, Id)`, you will be able to solve both your problems ;) Oh, and you'll need to add a `UNIQUE` constraints in `Accounts` for the above FK to be defined. – ypercubeᵀᴹ Mar 19 '15 at 14:05
  • Splitting the table into two seems the best solution for this. One would need only an FK to Users (and no AccountID column), the other only to Accounts. No multiple cascade paths and less nullable columns. – ypercubeᵀᴹ Mar 19 '15 at 14:18
  • Thanks for your suggestions. I know I can split it into two tables, though it's not so easy in my case cause this whole scenario is managed by Entity Framework. So the framework makes sure I haven't got a situation where I have different `Account` and `User` on one `BitcoinAddress`. But thanks for noticing, good catch! – Kelu Thatsall Mar 19 '15 at 14:50

1 Answers1

5

If you delete a user, his accounts will get deleted and his bitcoinaddresses will get deleted. But when his account gets deleted, the account's bitcoinaddresses will also get updated to NULL. That's too complicated for SQL Server. It needs the cascade chains to be simple and neat and not branch and/or converge. How can SQL-Server know which you want if a user deletion would result in both the deletion of bitcoin and setting its account to NULL?

ypercubeᵀᴹ
  • 113,259
  • 19
  • 174
  • 235
Tab Alleman
  • 31,483
  • 7
  • 36
  • 52
  • 2
    You are right, but even if I had set it to CASCADE delete on every REFERENCE then it would have given me the same error. – Kelu Thatsall Mar 19 '15 at 13:48
  • Yes and for the same reason. The two cascade paths converge. That's the root cause. SQL isn't programmed to think too deeply about whether converging cascades will actually cause a problem. If it sees potential convergence, it raises an error. – Tab Alleman Mar 19 '15 at 13:50
  • 2
    @TabAlleman Please edit your naswer, from "SQL" to "SQL-Server". There are othr SQL products (like Postgres) that work perfectly fine with multiple cascade paths. SQL-Server architects chose to forbid this (for some good reasons, no argue there). – ypercubeᵀᴹ Mar 19 '15 at 14:01
  • Thanks for the edit @ypercube. Unfortunately it's too late to make the same edit to my earlier comment. – Tab Alleman Mar 19 '15 at 16:19