The table/view does not have a primary key defined

Error:

The table/view does not have a primary key defined and no valid primary key could be inferred. This table/view has been excluded. To use the entity you will need to review your schema, add the correct keys and uncomment it.

Solutions:

The framework requires each entity to have a key. If no key is defined in the in the database object (like a view, or, as in your case, a table with no key), the tools try to infer a key based on the columns of the table or view (at least one non-binary, non-nullable column is required to infer a key). When no key can be inferred, a code comment that contains the corresponding EntityType element (with no Key elements) is added to the SSDL section of the .edmx file.

There are two solutions:

First Solution:

In your case, since it seems that you want a read only entity, you could…

1) Uncomment the SSDL entity

2) Mark one/some properties as Nullable=”False”

3) Add the appropriate Key elements

4) Add a corresponding defining query.

More detail in this forum thread about how to add a defining query.

Hope that helps.

Brian

Second Solution:

Insert to view a non-nullable column. You can use row number as a new column or modify the one of the non-binary current columns as a non-nullable. You can use ISNULL function to make the column as non-nullable.

Using rownumber as a new column:

SELECT ISNULL((ROW_NUMBER() OVER (ORDER BY YEAR DESC)), 0) AS ‘ID’, CITYNAME, YEAR, BRAND, SUM(SUMCOUNT) AS SALES

FROM         dbo.V_CITY_SALES_SUM

WHERE     (PRODUCT = ‘MOTORCYCLE’)

GROUP BY CITYNAME, YEAR, BRAND

If there is a “group by” clause do not make the grouped columns as a non-nullable column. It causes the all rows returned from the entity being the same!

Comments:

In the first solution you need to modify the schema but not the view and in every update you will lost the handmade modifications in the schema. In the second solution you need to modify the view only one time but not the schema so it seems better.

4 Replies to “The table/view does not have a primary key defined”

  1. for Oracle you should do this:
    1. CREATE OR REPLACE VIEW MYVIEW (“ID”)
    SELECT ROWNUM, … FROM …
    if you have an union, you will have to do;
    select rownum … from ENTITYT1
    union
    select rownum + (select count(*) from ENTITY1) … from ENTITY2
    2. alter view and add PK constraint:
    alter view MYVIEW add constraint id_pk primary key (ID) disable;
    3. update mode and see that the entity is generated.

Leave a Reply

Your email address will not be published. Required fields are marked *