视图上的 TSQL 外键?

2021-12-29 00:00:00 sql foreign-keys tsql sql-server views

我有一个 SQL-Server 2008 数据库和一个使用外键约束来强制执行参照完整性的架构.按预期工作.现在，用户在原始表上创建视图以仅处理数据的子集.我的问题是过滤某些表中的某些数据集而不过滤其他表中的某些数据集会违反外键约束.
想象两个表一"和二".one"仅包含一个值为 1、2、3 的 id 列.二"指一".现在您在两个表上创建视图.表two"的视图不过滤任何内容，而表one"的视图删除除第一行之外的所有行.您最终会在第二个视图中看到无处可去的条目.

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 已经解决了这个问题，但最好的解决方案是:

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

  1. 创建一次主"逻辑(过滤引用的表).
  2. 将相关表的所有视图连接到为 (1) 创建的视图，而不是原始表.
  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.

即，

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 标准的一部分.也就是说，这个解决方案仍然足够好——高效、直接、可维护，并保证使用代码的所需状态.

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.

相关文章