Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 3 down vote favorite
1

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.

Thanks!

share|improve this question

2 Answers

up vote 5 down vote accepted

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.

share|improve this answer
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 '12 at 18:20
2  
@TLama: You should also consider using no triggers at all :) – ypercube Apr 9 '12 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 '12 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. – ypercube Apr 9 '12 at 19:05
1  
SQL-Server has Computed Columns and Views that can be indexed, that may serve you needs, without the use of triggers. – ypercube Apr 9 '12 at 19:07
up vote 2 down vote

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.

share|improve this answer
+1ed, so that is valid also for SQL server, not only for MySQL ? – TLama Apr 9 '12 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. – Derek Downey Apr 9 '12 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 '12 at 21:32

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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