Results 1 to 6 of 6
  1. #1
    Join Date
    Feb 2004
    Location
    United Kingdom
    Posts
    6

    Red face 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

  2. #2
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    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?

  3. #3
    Join Date
    Mar 2003
    Location
    Australia
    Posts
    59
    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...

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    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.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  5. #5
    Join Date
    Feb 2004
    Location
    United Kingdom
    Posts
    6
    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

  6. #6
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    THAT's what I was waiting for!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •