.net – SQL Server视图可以有主键和外键吗？

是否可以在Microsoft SQL Server Management Studio中为数据库视图定义主键和外键？怎么样？

我正在尝试创建一个ADO.NET实体数据模型来读取我无法修改的四个旧的,格式不正确的数据库表.我已经创建了我需要的数据的视图.

这四个视图应该映射到一个简单的三实体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.

它正确推断出两个视图的主键.但未能与其他两个这样做.

我的一个问题视图使用聚合函数：

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

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

解决方法

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

视图中的任何行都应映射到表中的一行.

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.

（编辑：安阳站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!