SQL Server视图可以通过主键和外键？ [英] Can SQL Server views have primary and foreign keys?

问题描述

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

我试图创建一个ADO.NET实体数据模型从四老，我不能修改不当形成的数据库表来读取。我创建的意见只是我需要的数据。

这四个观点应该映射到一个简单的三个实体EDMX一个多对一对多的关系。

我创造我的数据模型时出现此错误：

  

表/视图'...'不具有   定义主键，也没有有效的   主键可以推断。本   表/视图已被排除。要使用   该实体将需要进行审查   您的模式，添加正确的密钥和   注释吧。

据推断正确的两种观点的主键。但未能与其他两个这样做。

我的一个问题的看法使用聚合函数：

  SELECT MAX（...）... GROUP BY ...
 

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

解决方案

您需要定义您的视图，以便它：

• 包括所有的 PRIMARY KEY 列
• 在不使用任何加入的
• 在不使用任何聚合函数或 UNION 的

这是你的看法任意行应该映射到从表中的一行。

  

我的一个问题的看法使用聚合函数

这不能更新。为只读实体，由溶液<一href="http://social.msdn.microsoft.com/Forums/ru-RU/adodotnetentityframework/thread/39fcc904-23b7-45c9-a2c8-7fe7a4a403c4">here:

  

当没有键可以推断，包含对应的EntityType元件（无关键要素）一个code注释被添加到.edmx文件的SSDL部分

     

在你的情况，因为它似乎想要一个只读的实体，您可以：

     

  
1. 取消对SSDL实体
  
2. 在商标申请一个/一些性质可空=FALSE
  
3. 添加相应的关键要素
  
4. 添加相应的定义查询。
  

对于第二个问题：

  

另外应该有两个外键复合主键

从 文件 ：

  

这是重新$ P $ A表psents数据库中的两个表之间的许多一对多的关系，可能没有在概念架构等同实体。当 EDM 工具会遇到这样的表没有列比二是外键等，映射表为psented的概念模式为多对一重新$ P $ to-many关联，而不是一个实体。

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.

解决方案

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
2. mark one/some properties as Nullable="False"
3. add the appropriate Key elements
4. 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.

这篇关于SQL Server视图可以通过主键和外键？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！