Sam S Kolli (3/22/2013)
Lynn...since D is not present in the parent table, none of A, B, D should be inserted even though A and B are present in the parent table.The overarching question I have is if I can rely on table definition to drive my logic in the stored proc. I can see the pros and cons, but I wanted to see how other people's experiences are like.
It is generally a better idea to use the constraints to control this type of thing. That is what they are designed to do. They will perform better in most cases than roll your own sql. And of course in your case you have the constraints so they will be checked even if you do it in code too. Then becomes the challenges of what happens if the business rules change? If you use constraints to control RI (which is the best approach) all you have to change is your constraints. If you use code you have to change your code in all locations AND your constraints.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/