Can SQL Server views have primary and foreign keys

Question

Is it possible to define primary and foreign keys for database Views in Microsoft SQL Server Management Studio? How?

I'm trying to create an ADO.NET Entity Data Model to read from four old, poorly-formed database tables that I cannot modify. I've created views of just the data I need.

The four views should map to a simple three-entity EDMX with one many-to-many relationship.

I get this error when creating 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 correctly inferred the primary keys of two views. But failed to do so with the other two.

One of my problem views uses aggregate functions:

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

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

Answer 1

You need to define your view so that it:

• Includes all the PRIMARY KEY columns
• Does not use any JOIN's
• Does not use any aggregate functions or UNION's

Any row from your view should map to exactly one row from the table.

One of my problem views uses aggregate functions

It cannot be updateable. For a readonly entity, a solution from here:

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.

For the second question:

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

From documentation:

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.

Answer 2

Actually, you can create a view that uses JOIN's and generate and Entity in your model from it.

Answer 3

You can alter your views by creating a NOT NULL index column in your view doing something like this:

ALTER VIEW [dbo].[ViewName]
AS
    SELECT  ISNULL(CAST(CASE ROW_NUMBER() OVER ( ORDER BY columnNames )
                          WHEN ROW_NUMBER() OVER ( ORDER BY columnNames )
                          THEN ROW_NUMBER() OVER ( ORDER BY columnNames )
                          ELSE 0
                        END AS INT), 0) AS ID