Evil Kraig F (4/26/2013)
Grant Fritchey (4/26/2013)
TheGreenShepherd (4/26/2013)
Don't mean to pile on here, but yeah. I fail to understand why someone would query this data in this fashion. Yeah, it improves performance for that query but it's unlikely that you'd ever actually write that query.Here's a blog post I wrote up[/url] that gives a different example, probably more realistic, to show how foreign keys help. They really do.
Grant, unfortunately, I'm of the same mind as the above. I checked out your blog post and really, there's not a lot of difference. If you don't have RI in place, you have to go to those tables to make sure you have data limiters, I agree. Once you have RI in place everything you did was off the 'p' table, including the where clause. He did a where exists, you did a join, but the limitation logic is the same.
Craig, I think you are seeing things froma very narrow perspective: a perspective where the people who write queries (a) write them against base tables, (b) know all the base table schemata. This is very different from the situation where application developers write queries against views and are not permitted to know the base schemata. In the latter situation, the optimiser is presented with a query whose from clause includes view expansions, which may be joining tables that it can eliminate because the particular query doesn't use anything from those tables.
Yes, it's very nice from the point of view of a DBA to be able to design all the queries himself - no app devs write queries, there is no facility which provides for ad hoc queries, and only queries written by the DBA team are ever run. But in the real world it isn't always like that. I'm a great believer in proper modularity and levels of abstractrion, and I don't believe an app developer should know the base schemata (mostly because if he does he'll write stuff that depends on them, so that further evolution of the base schemata becomes impossible - and this includes the case where what was supposed to be an ad hoc query is now the most important thing used by the marketing department or by the publicity department or by some one else with a lot of muscle); and I prefer to bury the schemata behind a stored procedure interface, rather than a view interface, in the hope of preclusing ad-hoc queries produced by all sorts of random people; but I can't guarantee to achieve that, and may have to provide some views that expose the data but not any teh structure of any part of any schema for which I have no real guarantee that the structure will not need to change in a few years time to enable us to accept new requirements, cope with new regulations, and enforce new business rules. Given that the requirement changes, the base schemata will need to change, so even if I start out exposing a view per base table (usually a fairly stupid idea, but not always) those views might rapidly cease to be in one to one correspondance with base tables.
Given these real world circumstances, where queries are written by people who don't know the underlying schemmata. proper declarative referential integrity enforcement gives the optimiser a great assist in getting the best possible performance out of the system for us.
Tom