.net - SQL Server View 可以有主键和外键吗？

Question

是否可以在 Microsoft SQL Server Management Studio 中定义数据库 View 的主键和外键？怎么办？

我正在尝试创建一个 ADO.NET 实体数据模型，以从四个我无法修改的旧的、格式不正确的数据库表中读取数据。我只创建了我需要的数据的 View 。

这四个 View 应映射到具有多对多关系的简单三实体 EDMX。

创建数据模型时出现此错误:

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.

它正确推断了两个 View 的主键。但其他两个却未能做到这一点。

我的问题 View 之一使用聚合函数:

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

另一个应该有两个外键的复合主键。

Answer 1

您需要定义您的 View ，以便:

• 包括所有PRIMARY KEY 列
• 不使用任何JOIN
• 不使用任何聚合函数或 UNION

View 中的任何行都应该映射到表中的一行。

One of my problem views uses aggregate functions

无法更新。对于只读实体，解决方案来自 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.

对于第二个问题:

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

来自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.