locked
Cascading changes in FK RRS feed

  • Question

  • I have not seen much use of FK using with the following options.

    ON DELETE/UPDATE [NO ACTION | CASCADE | SET NULL | SET DEFAULT]

    Can someone share their experience. Please explain in which senarious have you used noaction or cascade, set null or set default.

     

    Similarly, for check constraints is there a way to add when updated use default, eg. when insert, getdate(), similarly, without the use of a trigger, when updated, use getdate(). I am looking for a way to avoide using triggers here without chaning the app code. Currently I have an update trigger implementation.

    Thanks all.  

     

    Friday, September 23, 2011 4:22 PM

Answers

  • In my experience, NO ACTION is most commonly used. UPDATE CASCADE is useful when one uses natural keys instead of surrogate keys.  This allows changes to the referenced key to automatically propogate to the related rows.  DELETE CASCADE is useful with an identifying relationship when referencing data are to be deleted when the referenced row is deleted (e.g. delete order details when order is deleted).  DELETE SET NULL is handy when you want to retain referencing data when the referenced row is deleted (e.g. keep employees when their manager is deleted).

    You can use a DEFAULT constraint to set a value like GETDATE() if the column is not specified on the INSERT.  However, no such DEFAULT constraint functionality exists for UPDATE.

     


    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/
    Saturday, September 24, 2011 3:08 AM
    Answerer
  • They are not used because in most cases they are considered "Bad Idea"s. In a sense, they are there if your situation actually requires them, but should probably not be sued otherwise. Having a CASCADE DELETE is almost as bad as using a TRIGGER.
    Monday, September 26, 2011 1:29 PM
    Answerer

All replies

  • In my experience, NO ACTION is most commonly used. UPDATE CASCADE is useful when one uses natural keys instead of surrogate keys.  This allows changes to the referenced key to automatically propogate to the related rows.  DELETE CASCADE is useful with an identifying relationship when referencing data are to be deleted when the referenced row is deleted (e.g. delete order details when order is deleted).  DELETE SET NULL is handy when you want to retain referencing data when the referenced row is deleted (e.g. keep employees when their manager is deleted).

    You can use a DEFAULT constraint to set a value like GETDATE() if the column is not specified on the INSERT.  However, no such DEFAULT constraint functionality exists for UPDATE.

     


    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/
    Saturday, September 24, 2011 3:08 AM
    Answerer
  • They are not used because in most cases they are considered "Bad Idea"s. In a sense, they are there if your situation actually requires them, but should probably not be sued otherwise. Having a CASCADE DELETE is almost as bad as using a TRIGGER.
    Monday, September 26, 2011 1:29 PM
    Answerer
  • >Having a CASCADE DELETE is almost as bad as using a TRIGGER.

    Using a trigger is not bad, as long as you code them correctly and use
    them for their intended purpose.

    The same is true about cascading delete.


    Hugo Kornelis, SQL Server MVP
    My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
    Monday, September 26, 2011 1:38 PM
  • I disagree completely. My highly opinionated rule is that TRIGGERs are evil and used by poor coders. Indeed, i would never hire a developer that used TRIGGERs as it would point to his complete misunderstanding of databases and good practice. TRIGGERs are a kludge, and should only be used when there is no other choice...and even then they probably should not be used.

    CASCADE DELETE does make sense data-wise, but not implementation-wise, hence they are not as bad.


    Of course, you are entitled to your own opinion. :)




    • Edited by Brian TkatchEditor Monday, September 26, 2011 1:57 PM changed "crutch" to "kludge", while the former is true as well, it is not what i meant to say.
    Monday, September 26, 2011 1:48 PM
    Answerer
  • Hi Brian,

    My highly opinionated rule is that TRIGGERs are evil and used by poor coders.

    When used by poor coders, triggers are indeed evil. But the same can
    be said about cars - they are evil (and quite literally kill people)
    when used by bad drivers. But that does not mean that cars should be
    abolished.

    I have used triggers to accomplish things that could never have been
    accomplished without them. I fully stand by my choice to use them in
    those cases. If that means you won't hire me - well, so be it.

    CASCADE DELETE does make sense data-wise, but not implementation-wise, hence they are not/as/ bad.

    The problem with the implementation is that there is no way to control
    the order of locks, which might increase the chance of deadlocks. But
    in a system where activity is low enough that deadlocks are no huge
    obstacle, that should not be a problem.
    Do you see any other implementation problems with cascading deletes?


    Hugo Kornelis, SQL Server MVP
    My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
    Monday, September 26, 2011 2:12 PM
  • >I have used triggers to accomplish things that could never have been accomplished without them.

    I have yet to see a case where a TRIGGER can do something that cannot be done in an SP.

    In some situations, where there is a requirement to interface with a design from another group/company, a TRIGGER may be the only way (because they refuse to use an SP). That doesn't change the fact that an SP is better.

    >Do you see any other implementation problems with cascading deletes?

    Yes. There are no checks on what is about to be DELETEd. The (parent) record being DELETEd is always explicitly being done, the children are implicit, and can lead to human error. Hence, data-wise, it is correct, just not implementation-wise.

    Monday, September 26, 2011 3:17 PM
    Answerer
  • >I have yet to see a case where a TRIGGER can do something that cannot be done in an SP.

    A stored procedure will not automatically execute when data changes.
    Only triggers do that.

    If you're in a position where you can lock up your database so tight
    that all users get access through stored procedures only, then you
    don't need triggers (though you may end up duplicating stuff if
    changes come from multiple stored procedures).

    But sometimes you are in a situation where users can hit the table
    directly. Or, worse, where someone thinks he can develop applications
    because he once built a membership database in Access for his bowling
    club and now starts modifying the front end to suit his needs. In
    those cases, constraints and triggers are your only line of defense
    against inconsistent data.


    Hugo Kornelis, SQL Server MVP
    My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
    Tuesday, September 27, 2011 9:58 AM
  • That makes an interesting case for using them.

    Tuesday, September 27, 2011 12:55 PM
    Answerer
  • Another case for using triggers is to implement some mutli-row (set-based) constraint. For example you could implement a rule that the amount column in a  ledger table should always sum to zero. Business rules of that kind can be very hard to implement efficiently and reliably using a proc but are very easy and maintainable using triggers. Implementing such business rules is one of the few times when I will use a trigger - to validate the rule and rollback the transaction if the rule is violated. I always try to avoid using triggers that actually modify data.
    • Edited by navogel Tuesday, September 27, 2011 9:11 PM
    Tuesday, September 27, 2011 9:11 PM
  • Even when "very hard", i'll avoid TRIGGERs like the plague.

    To each their own.
    Monday, October 3, 2011 1:18 PM
    Answerer