I recall hearing Joel Spolsky mention in podcast 014 that he'd barely ever used an overseas key (basically remember properly). However, in my experience they appear pretty vital to prevent duplication and subsequent data integrity problems throughout your database.
Do individuals have some solid reasons why (to prevent attorney at law in lines with Stack Overflow principals)?
Good reasons to use Foreign Secrets:
- you will not get Orphaned Rows
- you will get nice "on remove cascade" behavior, instantly cleaning tables
- being aware of the associations between tables within the database helps the Optimizer plan your queries for many efficient execution, since with the ability to improve estimations on join cardinality.
- FKs provide a pretty large hint on which statistics are most significant to gather around the database, which results in better performance
- they enable a myriad of auto-produced support -- ORMs can generate themselves, visualization tools will have the ability to create nice schema designs for you personally, etc
- somebody new towards the project can get in to the flow of things faster since otherwise implicit associations are clearly recorded
Reasons to not use Foreign Secrets:
- you're making the DB work extra on every CRUD operation since it needs to check FK consistency. This is often a large cost for those who have lots of churn
- by enforcing associations, FKs specify a purchase that you need to add/remove things, be responsible for refusal through the DB to complete what you would like. (Granted, in such instances, what you're attempting to do is create an Orphaned Row, and that is not often a positive thing). This is particularly painful when doing large batch updates, and also you stock up one table before another, using the second table creating consistent condition (but for anyone who is doing that kind of factor if there's possible the second load fails as well as your database has become sporadic?).
- sometimes you realize in advance your computer data will probably be dirty, you believe that, and also you want the DB to simply accept it
- you're just being lazy :-)
I believe (I'm not certain!) that many established databases provide a method to specify an overseas key that's not enforced, and it is simply a little of metadata. Since non-enforcement baby wipes out every reason to not use FKs, you need to most likely go down that path if the reasons within the second section apply.
to quote Joe Celko,
"just like a size 26 thong, just since you can does not necessarily mean you need to!Inch
I am sure you will find lots of programs where one can pull off it, but its' not the very best idea. You cannot always rely on the application to correctly manage your database, and frankly controlling the database shouldn't be of greatly concern for your application.
If you work with a relational database it appears you need to have some associations defined inside it. Regrettably this attitude (you do not need foreign secrets) appears to become accepted by lots of application designers who'd rather 't be bothered with silly such things as data integrity (but have to as their companies do not have devoted database designers). Usually in databases come up with by these kinds you're lucky simply to have primary secrets )
Foreign secrets are essential to the relational database model.