locked
The view 'Schemaname.vwActiveProductCategory' does not have a primary key defined and no valid primary key could be inferred. RRS feed

  • Question

  • Hi ,

    I am getting an error - "The view 'Schemaname.vwActiveProductCategory' does not have a primary key defined and no valid primary key could be inferred."

    Below is the code to create view , ProductCategoryID is the primary key.

    Create view vwActiveProductCategory
                As
                SELECT pc.*, p.ProductCount
                FROM tbleProductCategory pc
                right outer JOIN
                (Select ProductCategoryID, count(ProductCategoryID)as ProductCount from  tbleProductInfo group by ProductCategoryID)p
                ON p.ProductCategoryID=pc.ProductCategoryID

    GO

    can any one explain what the problem is ?

    Saturday, February 26, 2011 6:53 PM

Answers

  • Hi!

    Just tested this, and I reproduce the same behaviour IF I use RIGHT OUTER JOIN. If I use LEFT OUTER JOIN it works as expected.

    I'm not quite sure why you want to use a RIGHT OUTER JOIN here. Could there be Product categories in tbleProductInfo that doesn't exist in tbleProductCategory?

    But anyway. A view in SQL Server does not have any primary keys, but it seems that it tries to detect if the primary key from the first table queried is selected. Since you use RIGHT OUTER JOIN the computed table is the first table, and this doesn't have any primary key nor any columns other than ProductCount selected in the view. I am NOT sure about this, maybe some other can give a better explanation on this!

    There are two different options to solve this. Either try to use LEFT OUTER JOIN or follow the description in this link http://msdn.microsoft.com/en-us/library/dd163156.aspx, the latter gives you the problem that each time you upgrades your model you will have to change it.

    Hope this helps,


    --Rune
    • Marked as answer by Jackie-Sun Wednesday, March 2, 2011 8:53 AM
    Sunday, February 27, 2011 1:55 PM

All replies

  • Hi!

    Just tested this, and I reproduce the same behaviour IF I use RIGHT OUTER JOIN. If I use LEFT OUTER JOIN it works as expected.

    I'm not quite sure why you want to use a RIGHT OUTER JOIN here. Could there be Product categories in tbleProductInfo that doesn't exist in tbleProductCategory?

    But anyway. A view in SQL Server does not have any primary keys, but it seems that it tries to detect if the primary key from the first table queried is selected. Since you use RIGHT OUTER JOIN the computed table is the first table, and this doesn't have any primary key nor any columns other than ProductCount selected in the view. I am NOT sure about this, maybe some other can give a better explanation on this!

    There are two different options to solve this. Either try to use LEFT OUTER JOIN or follow the description in this link http://msdn.microsoft.com/en-us/library/dd163156.aspx, the latter gives you the problem that each time you upgrades your model you will have to change it.

    Hope this helps,


    --Rune
    • Marked as answer by Jackie-Sun Wednesday, March 2, 2011 8:53 AM
    Sunday, February 27, 2011 1:55 PM
  • Hi,

    Sorry is not enough for such a late reply, i think i deserve a kick ! :)

    Yes you are right,  'Right Outer Join ' was casuing the probelm,  Right Outer Join i wanted because i dint want those categories, which has no product added, means count 0 or null

    but yes that i could have maneged by using left outer join also with an additional where clause as below.

     alter view [DBETNet].vwActiveProductCategory 
                As              
       SELECT pc.*, p.ProductCount 
                FROM tbProductCategory pc 
                left outer JOIN  
                (Select ProductCategoryID, count(ProductCategoryID)as ProductCount from  tbProductInfo group by ProductCategoryID)p 
                ON p.ProductCategoryID=pc.ProductCategoryID  where p.ProductCount  >0

    Thanks a lot
    -A

     

     


    Thanks Arindam Chakraborty .Net Designer & Developer.
    Thursday, March 31, 2011 12:54 PM