Advertisement
MatthijsFontys

Database foreign key constrains with cascade on delete

Jun 11th, 2019
234
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
T-SQL 1.04 KB | None | 0 0
  1. -- Remove elements not following foreign key constaints
  2. DELETE from dbo.User_Release where userId NOT IN (SELECT id from dbo.releaseUser)
  3. DELETE from dbo.Comment where dbo.Comment.releaseId NOT IN (SELECT id from dbo.Release)
  4. DELETE from dbo.User_Release where releaseId NOT IN (SELECT id from dbo.Release)
  5.  
  6. -- User release
  7. ALTER TABLE User_Release
  8. ADD FOREIGN KEY (userId)
  9. REFERENCES releaseUser(id)
  10. ON DELETE CASCADE;
  11.  
  12. ALTER TABLE User_Release
  13. ADD FOREIGN KEY (releaseId)
  14. REFERENCES Release(id)
  15. ON DELETE CASCADE;
  16.  
  17. -- Release
  18. ALTER TABLE Release
  19. ADD FOREIGN KEY (categoryId)
  20. REFERENCES Category(id)
  21. ON DELETE SET NULL;
  22.  
  23. ALTER TABLE Release
  24. ADD FOREIGN KEY (userId_Owner)
  25. REFERENCES releaseUser(id)
  26. ON DELETE SET NULL;
  27.  
  28. -- Comment
  29. ALTER TABLE Comment
  30. ADD FOREIGN KEY (releaseId)
  31. REFERENCES Release(id)
  32. ON DELETE CASCADE;
  33.  
  34. ALTER TABLE Comment
  35. ADD FOREIGN KEY (userId)
  36. REFERENCES releaseUser(id)
  37. ON DELETE CASCADE;
  38.  
  39. ALTER TABLE Comment
  40. ADD FOREIGN KEY (commentId_Reply)
  41. REFERENCES Comment(id)
  42. ON DELETE CASCADE;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement