I would like to ask about how to handle this relationship. For better view I've tried to simplify and convert my situation into how I think StackOverflow question voting has (sorry if not :-)

enter image description here

I would like to add a constraint with CASCADE DELETE rule however I don't know if it's possible for this case. Another option is to create an ON DELETE trigger, so...

My question is:
What would you prefer, CASCADE DELETE constraint rule (if it's possible here) or ON DELETE trigger ?
Or would you create a DB model for this simple case (imagine StackOverflow's voting scheme) different way ?

Why am I asking:
I've read somewhere that CASCADE DELETE constraint rules are internally nothing else than triggers and that some people here don't prefer the constraint rules at all. I know, quite vague question, but I would like to have my DB clean from a not existing relationship records.

The important thing:
I'm creating the application which should be able to run on the SQL Server as well as on MySQL database engines, but both should support all, the CASCADE DELETE constraint rules as well as ON DELETE triggers, I guess.


2 Answers 2


I don't think you will see a performance difference between trigger and using cascade. I would prefer ON CASCADE DELETE just because in my opinion it better describes model and reveals creator's intention. Also, if you later use one or another ORM, it will be able to build relationship between entities (surely, I'm talking about 'Data First' approach)if it's implemented through foreign key constraints, but not through the triggers.

  • Thanks! Just one sidenote; I haven't mentioned in my Q that I was planning to use triggers along with the cascade actions, but there's a note in MySQL 5.5. reference, cascaded foreign key actions do not activate triggers, so now I'm thinking about to use only triggers.
    – TLama
    Apr 9, 2012 at 18:20
  • 3
    @TLama: You should also consider using no triggers at all :) Apr 9, 2012 at 18:58
  • @ypercube, so you would prefer to delete related rows by your own. I like that however I need to perform some calculations over that when the rows are deleted, again back to SO, to calculate what is the overall score of the question (of course my case is much more complicated).
    – TLama
    Apr 9, 2012 at 19:01
  • I prefer DRI so I would use the ON DELETE CASCADE. But if you have much more complex needs, that can't be handled with declarative ways, I'd consider triggers. Apr 9, 2012 at 19:05
  • 2
    SQL-Server has Computed Columns and Views that can be indexed, that may serve you needs, without the use of triggers. Apr 9, 2012 at 19:07

The OP already mentioned this in a comment, but it is important enough to bring out in answer to the question.

In MySQL ON DELETE CASCADE will not activate any triggers on the child rows that were deleted in this manner. So, if you have any triggers on the child table, you will probably be better off using after delete triggers on the parent.

  • +1ed, so that is valid also for SQL server, not only for MySQL ?
    – TLama
    Apr 9, 2012 at 21:21
  • Sorry, clarified answer to only reflect MySQL limitation. I'm unsure if this behavior is in SQL Server, though I bet SQL Server issues triggers on cascade. Apr 9, 2012 at 21:29
  • I hope so, but nothing changes for me in this case, if I want to support both I need to use triggers. Thanks anyway!
    – TLama
    Apr 9, 2012 at 21:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.