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

问题描述

可以在Microsoft SQL Server Management Studio中为数据库视图定义主键和外键吗？如何？

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

这四个视图应该映射到一个简单的三实体EDMX与一对多关系。 p>

创建数据模型时收到此错误：

表/ ...没有定义
主键，不能推断有效的
主键。此
表/视图已被排除。要使用
实体，您将需要查看
您的模式，添加正确的键和
取消注释。

它正确地推断了两个视图的主键。但是另外两个没有这样做。

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

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

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

解决方案

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

• 包含所有 PRIMARY KEY 列


• 不使用任何 JOIN 的


• 不使用任何聚合函数或 UNION 的

您的视图中的任何行都应该从表中映射到正好一行。

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

它不能可更新。对于只读实体，来自 这里 ：

当没有键可以被推断时，包含相应的代码注释EntityType元素（没有键元素）被添加到.edmx文件的SSDL部分。

在您的情况下，由于您似乎想要一个只读实体，您可以：

1. 取消注释SSDL实体
   
   
   
   
   • 标记一个/一些属性为Nullable =False
   
   
   • 添加适当的Key元素
   
   
   • 添加相应的定义查询。
   
   

对于第二个问题： p>

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

从 文档 ：

表示数据库中两个表之间的多对多关系的表可能在概念模式中不具有等效实体。当 EDM 工具遇到这样一个没有列的外部表，而不是两个外键的表时，映射表在概念模式中被表示为多对多关联而不是一个实体。

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
   • 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.

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