January 13, 2005 at 10:49 pm
I would like to know more regarding impact on database where foreign keys not defined (both positive and negative efects) and usage of ie SP enstead, to enforce business rules.
- referential integrity
- query performance
- maintenance
etc etc.
Thank you,
Milovan
January 17, 2005 at 8:00 am
This was removed by the editor as SPAM
January 17, 2005 at 8:50 am
Okay, just some quick thoughts:
- Referential Integrity:
When you don't define relationships at the lowest level, but rather in a sp, they can be bypassed when someone doesn't use the sp.
Also, do you really think maintaining RI with a sp (and its pseudo-compiled T-SQL code) will be faster than highly optimized machine code enforcing RI?
- Query performance:
Shouldn't notice any difference as long as all proper indexes are in place.
- Maintainance:
Will become worse in larger databases. You will need a very disciplined approach in order not to lose yourself.
Why do you ask?
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
January 17, 2005 at 12:02 pm
I have never defined a db without it. I just can't imagine doing so. All i can picture is two-three years down the road all kinds of orphaned rows.
Just remember, SP is not the only way to a database table and neither is your application. Unfortunately, you cannot control the actions of others or even your own accidental actions.
If you accidentally delete a row in the primary table, your foreign key table is out of luck. And, you have no restriction. With a FK defined, you would not be able to delete the primary row unless you have a cascade delete defined.
In my opinion, it's always best to take advantage of constraints. Why not let the server do the work for you?
January 20, 2005 at 11:09 pm
Thank you very much for your answers. That's exactly what I thought. I will use your opinions as a valid answers to my Project Manager that insists of creating database within new project with no FK defined and with concept of data integrity maintenance through SP.
Thanks,
Milovan
January 21, 2005 at 7:05 am
It is all a matter of preference sometimes and lack of formal disipline and knowledge, but the point of FKeys is to enforce referential integrity at the table level.
I myself many times will bypass only when it can be contained otherwise.
For example if I have a support table which has a fixed list of potential values I can put a Constraint on the column instead wih the Range if I am using say a number list.
This will avoid a read or two on insert and updates.
However when you don't have a fixed list the amount of work to ensure and cleanup against mistakes is entirely too much to worry about. Especially on more complicated system.
The application should be oblivous to integrity and it should be enforced at as close to the data as possible.
Yes there are performance concerns which removing can help with but should really only be considered in high end disposable data situations and data warehouses (data should have been sanity checked beforehand anyway here).
Unless you can show a noticeable improvement by not having them and can prove the extra work to ensure integrity of the data doesn't actually cost more than using them and you are maticulous in your documentation and rules to ensure once you leave nothing will go wrong use the FKeys and be smart in your work.
Half-@$$ doin the job is not really doing the job in the first place and that is what you are paid for (personal opinion here no real relavence).
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply