视图上的TSQL外键？ [英] TSQL foreign keys on views?

问题描述

我有一个SQL-Server 2008数据库和一个使用外键约束来强制引用完整性的模式。按预期工作。现在，用户只能在原始表上创建视图来处理数据的子集。我的问题是过滤某些表中的某些数据集，而不是其他数据集将违反外键约束。

想象两个表一和两。 one只包含一个值为1,2,3的id列。 两引用一。现在你在这两个表上创建视图。表two的视图不会过滤任何内容，而表one的视图将除去第一个视图中的所有行。你会最终在第二个视图中指向任何地方的条目。

有没有办法避免这种情况？您可以在视图之间有外键约束吗？

一些澄清在回应一些意见：

I' m意识到底层约束将确保即使在通过视图插入时数据的完整性。我的问题在于消耗观点的陈述。这些陈述是在原始表格的基础上编写的，并且假设某些连接不能失败。这个假设在处理表格时总是有效的 - 但是视图可能会破坏它。

首先创建视图时连接/检查所有约束是由于大量的引用表引起的。因此，我希望能够避免这种情况发生。

解决方案

彼得已经打了这个，但最好的解决办法是：

1. 创建main逻辑（过滤被引用的表）一次。
   
2. 具有相关表的所有视图加入为（1）创建的视图，而不是原始表。
>



  CREATE VIEW v1 AS SELECT * FROM table1 WHERE blah 
 
 CREATE VIEW v2 AS SELECT * FROM table2 WHERE EXISTS 
（SELECT NULL FROM v1 WHERE v1.id = table2.FKtoTable1）
  

当然，语法糖用于将一个表上的视图的过滤器传播到下级表上的视图将是方便的，但是，它并不是SQL标准的一部分。也就是说，这个解决方案仍然足够好 - 高效，简单，可维护，并保证消费代码所需的状态。




I have a SQL-Server 2008 database and a schema which uses foreign key constraints to enforce referential integrity. Works as intended. Now the user creates views on the original tables to work on subsets of the data only. My problem is that filtering certain datasets in some tables but not in others will violate the foreign key constraints.
Imagine two tables "one" and "two". "one" contains just an id column with values 1,2,3. "Two" references "one". Now you create views on both tables. The view for table "two" doesn't filter anything while the view for table "one" removes all rows but the first. You'll end up with entries in the second view that point nowhere.

Is there any way to avoid this? Can you have foreign key constraints between views?

Some Clarification in response to some of the comments:
I'm aware that the underlying constraints will ensure integrity of the data even when inserting through the views. My problem lies with the statements consuming the views. Those statements have been written with the original tables in mind and assume certain joins cannot fail. This assumption is always valid when working with the tables - but views potentially break it.
Joining/checking all constraints when creating the views in the first place is annyoing because of the large number of referencing tables. Thus I was hoping to avoid that.

解决方案

Peter already hit on this, but the best solution is to:

1. Create the "main" logic (that filtering the referenced table) once.
2. Have all views on related tables join to the view created for (1), not the original table.

I.e.,

CREATE VIEW v1 AS SELECT * FROM table1 WHERE blah

CREATE VIEW v2 AS SELECT * FROM table2 WHERE EXISTS
  (SELECT NULL FROM v1 WHERE v1.id = table2.FKtoTable1)

Sure, syntactic sugar for propagating filters for views on one table to views on subordinate tables would be handy, but alas, it's not part of the SQL standard. That said, this solution is still good enough -- efficient, straightforward, maintainable, and guarantees the desired state for the consuming code.

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