var sidebar_align = 'right';
var content_container_margin = parseInt('290px');
var sidebar_width = parseInt('270px');
Unanswered: primary key in aggregated view
Been pulling my hair out with this one for some time now ... hope someone out there can help
I have a database view which is an aggregated view of a number of tables. Trouble is I need to create what would effectively be a primary key for the view.
Can anyone suggest a sound way of doing this other than moving to Oracle
If you already have an alternative, - may god help you (it won't be my god though )
Why do you need a PK on a view? Using it instead of a table?
A view (should) "inherit" all the contraints that are expressed in the uderlying query. Unfortuneately, because SQL allows duplicates, the result set of the view cannot be guareented to contain a key.
As a possible work around, look at Indexed views if you have SQL2K.. you may be able to add a unique index to the view if it conforms...
Yeah, why do you need a primary key on your view?
Truth is, I hardly ever use views any more. I find that either table variables, temporary tables, or table functions are more effective.
The view aggregates a number of child tables which contain amounts by currency etc
I also have a second view which is effectively a breakdown of the contents of the aggregation in the first view.
Because the second view is a child of the first, I needed a consistent primary key to link them together.
I solved it by creating a function that took the MIN primary key of the instances that would be in the breakdown view and passed this back to the first view.
It works a treat, just not sure how it will perform yet
Thanks for your help
THAT's what I was waiting for!