2

Please take a look at the diagram below: Diagram

This all ties around if a user is deleted.

1. If a user is deleted, and the user has a thread. The thread will be deleted.
2. If a user does not have a thread, but has posts, the posts will be deleted.
3. If a thread is deleted, ALL posts on that thread is deleted.

I tried to cascade User->Thread, User->Post, Thread->Post. However, I get this exception: Introducing FOREIGN KEY constraint may cause cycles or multiple cascade paths. Specify on delete no action.

I sort of understand the error. If a user is deleted, both thread and post is deleted, so when thread is deleted, it will try to cascade post, which has already been cascaded by user. (Is this right?)

So if the above is true, how can I solve this?

FerX32
  • 1,407
  • 3
  • 18
  • 28
  • 1
    Possible duplicate of [Foreign key constraint - how to delete referenced record?](https://stackoverflow.com/questions/48540509/foreign-key-constraint-how-to-delete-referenced-record) It's explained how you can define custom actions on such scenario :) – Michał Turczyn Feb 13 '18 at 06:14

2 Answers2

1

There is an alternate solution where you have to set FK to null when PK is deleted as below. In relationship there is group of Table Designer in which option of Insert and Update specification under it you will find Delete Rule, set it to NULL.

This means in your scenario when user is deleted in thread table user_account_id column value replace with NULL and same in post table user_account_id column value replace with NULL. When you delete any thread it also affect post table and column user_account_id value will replace with NULL.

enter image description here

Sandip - Frontend Developer
  • 14,397
  • 4
  • 35
  • 61
1

Revised

So from the discussion below it sounds like: When a user is deleted, you want to delete his threads and any posts (his or anyone else's) associated with that thread, and also any of his posts that may be in other people's threads. By design, that gives us 2 paths for deleting rows in the post table hence, the problem.

Just googled it and found a very in-depth discussion of this exact problem on an obscure site called stackoverflow :-)

Introducing FOREIGN KEY constraint may cause cycles or multiple cascade paths - why?

The above discussion is both deep and broad and well worth reading. This won't do it justice but after being prodded by LuFFy below, I decided to add some highlights. The thrust of the number 1 answer is basically what I already said above - there are 2 cascading delete paths to the same table.

One solution is: don't do that, turn off the delete rule for one of the FK's on the post table. Another solution is to set one of the the delete rules to "set null" as described (in better detail) by Sandip Patel in the other response here.

I hope this helps.

jwolf
  • 908
  • 7
  • 13
  • 2
    Another user could have created the thread. The user only posted in that person's thread. Basically, just a comment. – FerX32 Feb 13 '18 at 05:45
  • @FerX32 - Ahhh. – jwolf Feb 13 '18 at 05:51
  • 1
    @FerX32 - Then, when a user is deleted, you want to delete his threads and any posts (his or anyone else's) associated with that thread, and also any of his posts that may be in other peoples threads? – jwolf Feb 13 '18 at 05:55
  • Yup! Exactly as you've said. – FerX32 Feb 13 '18 at 05:59
  • While this link may answer the question, it is better to include the essential parts of the answer here and provide the link for reference. Link-only answers can become invalid if the linked page changes. - [From Review](/review/low-quality-posts/18804971) – LuFFy Feb 13 '18 at 06:39
  • 1
    @LuFFy - Agreed, but if you look at that discussion, it is both broad and deep (180+ upvotes on the question alone) and with multiple solutions - way too much to from me to cliff-note it here. – jwolf Feb 13 '18 at 06:50
  • @jwolf, Try to add summary if possible, not whole answer :) – LuFFy Feb 13 '18 at 06:52