November 29, 2007 at 7:35 am
I'm going to simply answer the question posed in the title.
Foreign Keys are a good practice. At the very least there is always at least 2 applications that can modify data. The one written for end users and the ones DBA's and, sometimes, developers use, Query Analyzer/EM/SSMS. Foreign Keys mean I as the DBA can't make a mistake and mess up the data!
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
November 29, 2007 at 11:02 am
hey jeff...
Can you post the link of the forum u mentioned about ?
And hey i did not quite get that 2 Applications thing .... Cause there were always be two applications right .... one is the UI and the second which DBA developers use is the SQL Server management studio is it not ???? so how does this rule apply ......
Thanks
November 29, 2007 at 11:24 am
foriegn keys are good to have as it ensure data entigrity....though it will afeect performance slightly....
I normally put it while in developement, but then go ahead and remove it once product get stablized
Prakash Heda
Lead DBA Team - www.sqlfeatures.com
Video sessions on Performance Tuning and SQL 2012 HA
November 29, 2007 at 11:40 am
hey jeff...
Can you post the link of the forum u mentioned about ?
SQLServerCentral.com 😉
--Jeff Moden
November 29, 2007 at 11:45 am
Jack Corbett (11/29/2007)
I'm going to simply answer the question posed in the title.Foreign Keys are a good practice. At the very least there is always at least 2 applications that can modify data. The one written for end users and the ones DBA's and, sometimes, developers use, Query Analyzer/EM/SSMS. Foreign Keys mean I as the DBA can't make a mistake and mess up the data!
Preach on Brother Jack... I fully need every measure I can get to protect my data from me having a bad day, a senior moment, or whatever you want to call a lapse in judgement.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
November 29, 2007 at 11:49 am
This whole debate is absurd.
Removing DRI for performance reasons is a very bad idea. Relational databases are not used for performance reasons; they are used to store the data in a structured format that is convenient and safe to use. Nothing is more inconvenient than data that does not make sense. “Your inventory is stored in a location that does not exist.” “A customer order is shipping products that do not exist to a customer that does not exist.” “Your money is in an account that does not exist.” What do you think the performance penalty is for these conditions?
Removing DRI for performance reasons makes no more sense than not running backups for performance reasons.
If performance is the ultimate goal, just shove the data into a flat file.
November 29, 2007 at 12:06 pm
Prakash Heda (11/29/2007)
I normally put it while in developement, but then go ahead and remove it once product get stablized
In >25 years in this business, I've never seen a product that stable.
That's like putting in railings on stairways and balconies while you're building the hotel, but then removing them once you're open and guests have checked in.
You're removing them at precisely the time they are most needed!
Tomm Carr
--
Version Normal Form -- http://groups.google.com/group/vrdbms
November 30, 2007 at 2:32 am
Apologies if this has been said but it's a long topic to skim. I'm a .NET developer as well as a sql dba so see both sides.
The original post mentioned this was a .NET application, so if the data is being handled in a dataset(effectively an in-memory database) with datatables then the relationships could equally well be set up there and enforced by the .NET thus having all the desired effects in the data access layer and the advantage of allowing multiple platforms. There's considerable power in .NET datatables, datarelations, foreign keys,primary keys and constraints that can be all done programmatically.
DON'T change the database without discussions with the developers about the overall design and access methods, you could easily unintentionally break something and diplomacy should be your way forward not the bull-in-a-china-shop approach.
November 30, 2007 at 5:34 am
P Jones (11/30/2007)
There's considerable power in .NET datatables, datarelations, foreign keys,primary keys and constraints that can be all done programmatically.
Works like a charm too... until the first time a request comes through to do a manual update to a piece of data and now, you're operating without a net.
It also assumes that developers are completely and utterly, without fail, perfect. They don't miss a constraint, ever. I'm not suggesting that DBA's don't miss them or screw up in some way. I'm assuming that I'm going to screw up somewhere, on something, daily, hourly sometimes. That's why I have backups, log backups, data types, required not-null columns, primary keys, foreign keys, unique constraints, defaults, other constraints, triggers where necessary, stored procedures with defined parameter sets... Because I know I'm going to make a mistake and I need to mitigate that risk as much as possible. All this in order to protect the data that my company uses to make the money from which I get paid.
I'm impressed that so many people operate all day, every day, 24/7, without error. You guys are good. You should ask for a raise. And teach the rest of us the secret of perfection.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
November 30, 2007 at 6:17 am
Grant - agreed. That's why my own .NET applications only reflect the underlying database structure not replace it. But we're all different so be aware of other ways even if they aren't the best. Or of course, risk losing your job by upsetting others...... plenty of examples around of that!!
November 30, 2007 at 6:54 am
Sorry, I wasn't trying to upset anyone. I'm serious. I screw up all the time. The checks I can put in place to prevent my own errors will help those that are less error prone than myself. The deal is, you don't need FK's on your database. Code, either out in .NET or within TSQL, Java, what have you, can handle all the constraints without the need for FK's. No question.
It's not a question of what can be done. It's a question of risk and mitigation (can you tell I work at an insurance company?). How important is your data? If it's not important at all, hell, do what you want. If you're counting on it to make money, not placing as many safety checks on it as you reasonably can just seems stupid. That's not questioning anyone's ability. God knows, just about everyone posting on this discussion is more skilled and capable than I am. But no matter how skilled and capable, there's that one moment when you blink, look away, talk to a co-worker, had a bad night's sleep, sun spots, and you hit the wrong key on the keyboard and you've just introduced an error in your code, or ran an improper script or something. Having the backstop of all that crap on the database, despite the fact that it's a pain the butt to maintain, despite the fact that it can slow down inserts & updates, despite the fact that it means you can't use LINQ the way you want to, whatever. It saves your ***. Suddenly, it's all worth it.
Again, if my implication at perfection was offensive, I apologize.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
November 30, 2007 at 7:38 am
Grant Fritchey (11/30/2007)
It also assumes that developers are completely and utterly, without fail, perfect. They don't miss a constraint, ever. I'm not suggesting that DBA's don't miss them or screw up in some way. I'm assuming that I'm going to screw up somewhere, on something, daily, hourly sometimes.
I sure know I have missed a few in the past. However, 2 years ago we started using a formal method of design which includes a Business Requirements Document and Functional Requirements document. I then turn it into a checklist to help me keep track and anything missed generally is the business units issue as they did not cover it in their requirements. Get's the 90% of the mistakes attributed to me on the backs of those who actually had been at fault. Even thou off topic you should adopt a formal method of design and stick to it, makes life so much better. 😀
November 30, 2007 at 7:50 am
Oh and btw somone made a decent point about OLTP and OLAP. DRI is very important for OLTP systems. For OLAP thou the data for most systems is generally static and many time you denormalize for performance. I will say for an OLAP system primarily used in business anaylisis and reporting that DRI isn't necessarily as important becuase of the static nature of the data and the mass loads. However, for OLTP or systems where you have to manage with changs to the data it is a best practice. But don't get your OLAP and your OLTP systems into a grey area as a database used for reporting only with no direct transactions other than loading of historical information for reporting and analysis whether or not you use Analysis Services still falls more to the OLAP category and not OLTP.
Viewing 13 posts - 76 through 87 (of 87 total)
You must be logged in to reply to this topic. Login to reply