1

I'm creating a website, and I want to allow users to give reputations one to another. So I have 2 FOREIGN KEYs referencing to User table.

enter image description here

With this, I cannot apply ON DELETE and ON UPDATE on CASCADE on both CONSTRAINTS - it allows me to do it on one only. So this gives me a problem, I cannot delete my user and to delete all given and taken reputation automatically. It doesn't even let me delete a user at all. Any advice?

Darko Mitic
  • 47
  • 7
  • What server is this, mysql? – Drew Jul 24 '16 at 22:49
  • No, it's SQL Server. – Darko Mitic Jul 24 '16 at 23:31
  • 1
    Ok, tweak the tags then – Drew Jul 24 '16 at 23:38
  • If you want to see a soft delete, for mysql, I will post that. – Drew Jul 24 '16 at 23:42
  • My bad. Any idea on problem? Should I go with "NO ACTION" and then, before actually removing an User from database, remove stuff that he did first(posts, reports, voteup/downs, reputations, etc..)? – Darko Mitic Jul 24 '16 at 23:44
  • What is a "soft delete" though? – Darko Mitic Jul 24 '16 at 23:45
  • You mark a user row as inactive, other parts of your code know not to display the user. But the row remains – Drew Jul 24 '16 at 23:46
  • You don't think Stackoverflow actually deletes users, do you ? – Drew Jul 24 '16 at 23:47
  • Yeah, it crossed my mind. But it will create me bigger problem, cus user would like to delete everything that he did on website when he deletes the account, and with that, i will have users with bigger reputation, even tho the user that did the reputation does not exist. But I guess I could try. Thanks for the tip. – Darko Mitic Jul 24 '16 at 23:52
  • Just delete the stuff you want deleted, soft-delete the user, set his rep to whatever you want, etc etc. FK chain will still be honored – Drew Jul 25 '16 at 00:00
  • Anyway, I was looking if anyone has a solution, because I'm sure I'm not the only one that uses 2 foreign keys from same table in other table. I'll figure it out with that "soft delete" I guess. Thanks. EDIT: Yes, I'm gonna do that. – Darko Mitic Jul 25 '16 at 00:02
  • 1
    Well that is what people do if you want to honor the FK(s) going into the same table :p Edit: it is possible to allow NULL on some setups. – Drew Jul 25 '16 at 00:06

0 Answers0