Are primary and foreign keys possible in SQL Server views?

.net ado.net-entity-data-model database-design entity-framework sql-server

Question

In Microsoft SQL Server Management Studio, are primary and foreign keys for database views feasible to define? How?

I'm attempting to build an ADO.NET Entity Data Model to read from four outdated, improperly constructed database tables that I am unable to change. Just the views I need have been produced.

The four views ought to correspond to a straightforward three-entity EDMX with a single many-to-many connection.

I encounter the following issue while building my data model:

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.

It properly deduced the two views' main keys. the other two, however, she was unable to do so.

One of my issue viewpoints makes use of aggregate operations:

SELECT MAX(...) ... GROUP BY ...

The other should have a main key that is a composite of two foreign keys.

1
9
2/18/2011 5:24:09 PM

Popular Answer

You must provide your position so that it:

  • contains all thePRIMARY KEY columns
  • not using anyJOIN 's
  • uses neither aggregate functions norUNION 's

Every row in your view must correspond to precisely one row in the table.

One of my problem views uses aggregate functions

It is not upgradable. An answer from here for a read-only object is:

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.

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

  1. uncomment the SSDL entity
    • mark one/some properties as Nullable="False"
    • add the appropriate Key elements
    • add a corresponding defining query.

Regarding the second query:

The other ought to have a compound primary key of two foreign keys

From 57 to zzz:

A table that represents a many-to-many relationship between two tables in the database may not have an equivalent entity in the conceptual schema. When the EDM tools encounter such a table with no columns other than the two that are foreign keys, the mapping table is represented in the conceptual schema as a many-to-many association instead of an entity.

7
4/13/2009 9:19:20 PM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow