SQL 视图上的 TSQL 外键？

TSQL foreign keys on views?

提问by BuschnicK

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.

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

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

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

Some Clarificationin 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.

针对某些评论的一些澄清：
我知道，即使在通过视图插入时，基础约束也将确保数据的完整性。我的问题在于消耗视图的语句。这些语句是在考虑原始表的情况下编写的，并假设某些连接不会失败。这个假设在使用表格时总是有效的 - 但视图可能会破坏它。
由于大量引用表，首先在创建视图时加入/检查所有约束是很麻烦的。因此，我希望避免这种情况。

采纳答案by richardtallent

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 viewcreated for (1), not the original table.

1. 创建一次“主”逻辑（过滤引用的表）。
2. 将相关表的所有视图连接到为 (1) 创建的视图，而不是原始表。

I.e.,

IE，

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.

当然，用于将一个表上的视图的过滤器传播到从属表上的视图的语法糖会很方便，但可惜，它不是 SQL 标准的一部分。也就是说，这个解决方案仍然足够好——高效、直接、可维护，并保证使用代码的所需状态。

回答by Rob Farley

I love your question. It screams of familiarity with the Query Optimizer, and how it can see that some joins are redundant if they serve no purpose, or if it can simplify something knowing that there is at most one hit on the other side of a join.

我喜欢你的问题。它尖叫着熟悉查询优化器，以及它如何看到一些连接是多余的，如果它们没有用处，或者如果知道在连接的另一侧最多有一次命中，它可以简化某些事情。

So, the big question is around whether you can make a FK against the CIX of an Indexed View. And the answer is no.

因此，最大的问题是您是否可以针对索引视图的 CIX 进行 FK。答案是否定的。

create table dbo.testtable (id int identity(1,1) primary key, val int not null);
go
create view dbo.testview with schemabinding as
select id, val
from dbo.testtable
where val >= 50
;
go
insert dbo.testtable
select 20 union all
select 30 union all
select 40 union all
select 50 union all
select 60 union all
select 70 
go
create unique clustered index ixV on dbo.testview(id);
go
create table dbo.secondtable (id int references dbo.testview(id));
go

All this works except for the last statement, which errors with:

除了最后一条语句外，所有这些都有效，该语句出错：

Msg 1768, Level 16, State 0, Line 1
Foreign key 'FK__secondtable__id__6A325CF7' references object 'dbo.testview' which is not a user table.

So the Foreign key must reference a user table.

所以外键必须引用一个用户表。

But... the next question is about whether you could reference a unique index that is filtered in SQL 2008, to achieve a view-like FK.

但是……下一个问题是关于您是否可以引用在 SQL 2008 中过滤的唯一索引，以实现类似视图的 FK。

And still the answer is no.

答案仍然是否定的。

create unique index ixUV on dbo.testtable(val) where val >= 50;
go

This succeeded.

这成功了。

But now if I try to create a table that references the valcolumn

但是现在如果我尝试创建一个引用该val列的表

create table dbo.thirdtable (id int identity(1,1) primary key, val int not null check (val >= 50) references dbo.testtable(val));

(I was hoping that the check constraint that matched the filter in the filtered index might help the system understand that the FK should hold)

（我希望与过滤索引中的过滤器匹配的检查约束可能有助于系统理解 FK 应该保持）

But I get an error saying:

但我收到一条错误消息：

There are no primary or candidate keys in the referenced table 'dbo.testtable' that matching the referencing column list in the foreign key 'FK__thirdtable__val__0EA330E9'.

If I drop the filtered index and create a non-filtered unique non-clustered index, then I can create dbo.thirdtable without any problems.

如果我删除过滤索引并创建一个非过滤的唯一非聚集索引，那么我可以毫无问题地创建 dbo.thirdtable。

So I'm afraid the answer still seems to be No.

所以恐怕答案似乎仍然是否定的。

回答by Damir Sudarevic

It took me some time to figure out the misunderstaning here -- not sure if I still understand completely, but here it is. I will use an example, close to yours, but with some data -- easier for me to think in these terms.

我花了一些时间才弄清楚这里的误解 - 不确定我是否仍然完全理解，但就是这样。我将使用一个接近您的例子，但有一些数据——我更容易用这些术语来思考。

So first two tables; A = Department B = Employee

所以前两张表；A = 部门 B = 员工

CREATE TABLE Department
  ( 
   DepartmentID int PRIMARY KEY
  ,DepartmentName varchar(20)
  ,DepartmentColor varchar(10)
  )
GO 
CREATE TABLE Employee
  ( 
   EmployeeID int PRIMARY KEY
  ,EmployeeName varchar(20)
  ,DepartmentID int FOREIGN KEY REFERENCES Department ( DepartmentID )
  )
GO 

Now I'll toss some data in

现在我要扔一些数据

INSERT  INTO Department
  ( DepartmentID, DepartmentName, DepartmentColor )
 SELECT 1, 'Accounting', 'RED' UNION
 SELECT 2, 'Engineering', 'BLUE' UNION
 SELECT 3, 'Sales', 'YELLOW'  UNION
 SELECT 4, 'Marketing', 'GREEN' ;

INSERT  INTO Employee
  ( EmployeeID, EmployeeName, DepartmentID )
 SELECT 1, 'Lyne', 1 UNION
 SELECT 2, 'Damir', 2 UNION
 SELECT 3, 'Sandy', 2 UNION
 SELECT 4, 'Steve', 3 UNION
 SELECT 5, 'Brian', 3 UNION
 SELECT 6, 'Susan', 3 UNION
    SELECT 7, 'Joe', 4 ;

So, now I'll create a view on the first table to filter some departments out.

所以，现在我将在第一个表上创建一个视图来过滤掉一些部门。

CREATE VIEW dbo.BlueDepartments
AS
SELECT * FROM dbo.Department
WHERE DepartmentColor = 'BLUE'
GO

This returns

这返回

DepartmentID DepartmentName       DepartmentColor
------------ -------------------- ---------------
2            Engineering          BLUE

And per your example, I'll add a view for the second table which does not filter anything.

根据您的示例，我将为第二个表添加一个不过滤任何内容的视图。

CREATE VIEW dbo.AllEmployees
AS
SELECT * FROM dbo.Employee
GO

This returns

这返回

EmployeeID  EmployeeName         DepartmentID
----------- -------------------- ------------
1           Lyne                 1
2           Damir                2
3           Sandy                2
4           Steve                3
5           Brian                3
6           Susan                3
7           Joe                  4

It seems to me that you think that Employee No 5, DepartmentID = 3 points to nowhere?

在我看来，您认为 Employee No 5, DepartmentID = 3 指向无处？

"You'll end up with entries in the second view that point nowhere."

“你最终会在第二个视图中得到无处可去的条目。”

Well, it points to the Departmenttable DepartmentID = 3, as specified with the foreign key. Even if you try to join view on viewnothing is broken:

好吧，它指向Departmenttable DepartmentID = 3，正如外键指定的那样。即使您尝试加入 view on view也没有任何问题：

SELECT  e.EmployeeID
       ,e.EmployeeName
       ,d.DepartmentID
       ,d.DepartmentName
       ,d.DepartmentColor
FROM    dbo.AllEmployees AS e
        JOIN dbo.BlueDepartments AS d ON d.DepartmentID = e.DepartmentID
        ORDER BY e.EmployeeID

Returns

退货

EmployeeID  EmployeeName         DepartmentID DepartmentName       DepartmentColor
----------- -------------------- ------------ -------------------- ---------------
2           Damir                2            Engineering          BLUE
3           Sandy                2            Engineering          BLUE   

So nothing is broken here, the join simply did not find matching records for DepartmentID <> 2This is actually the same as if I join tablesand then include filteras in the first view:

所以这里没有任何问题，连接根本没有找到匹配的记录DepartmentID <> 2这实际上就像我连接表然后在第一个视图中包含过滤器一样：

SELECT  e.EmployeeID
       ,e.EmployeeName
       ,d.DepartmentID
       ,d.DepartmentName
       ,d.DepartmentColor
FROM    dbo.Employee AS e
        JOIN dbo.Department AS d ON d.DepartmentID = e.DepartmentID
        WHERE d.DepartmentColor = 'BLUE'
     ORDER BY e.EmployeeID

Returns again:

再次返回：

EmployeeID  EmployeeName         DepartmentID DepartmentName       DepartmentColor
----------- -------------------- ------------ -------------------- ---------------
2           Damir                2            Engineering          BLUE
3           Sandy                2            Engineering          BLUE

In both cases joins do not fail, they simply do as expected.

在这两种情况下，连接都不会失败，它们只是按预期进行。

Now I will try to break the referential integrity through a view (there is no DepartmentID= 127)

现在我将尝试通过视图打破参照完整性（没有 DepartmentID=127）

INSERT  INTO dbo.AllEmployees
      ( EmployeeID, EmployeeName, DepartmentID )
VALUES( 10, 'Bob', 127 )

And this results in:

这导致：

Msg 547, Level 16, State 0, Line 1
The INSERT statement conflicted with the FOREIGN KEY constraint "FK__Employee__Depart__0519C6AF". The conflict occurred in database "Tinker_2", table "dbo.Department", column 'DepartmentID'.

If I try to delete a department through the view

如果我尝试通过视图删除部门

DELETE FROM dbo.BlueDepartments
WHERE DepartmentID = 2

Which results in:

结果是：

Msg 547, Level 16, State 0, Line 1
The DELETE statement conflicted with the REFERENCE constraint "FK__Employee__Depart__0519C6AF". The conflict occurred in database "Tinker_2", table "dbo.Employee", column 'DepartmentID'.

So constraints on underlying tables still apply.

因此对基础表的约束仍然适用。

Hope this helps, but then maybe I misunderstood your problem.

希望这会有所帮助，但也许我误解了您的问题。

回答by Damir Sudarevic

If you try to insert, update or delete data through a view, the underlying table constraints still apply.

如果您尝试通过视图插入、更新或删除数据，基础表约束仍然适用。

回答by Tom H

Something like this in View2 is probably your best bet:

View2 中的类似内容可能是您最好的选择：

CREATE VIEW View2
AS
     SELECT
          T2.col1,
          T2.col2,
          ...
     FROM
          Table2 T2
     INNER JOIN Table1 T1 ON
          T1.pk = T2.t1_fk

回答by WaitForPete

If rolling over tables so that Identity columns will not clash, one possibility would be to use a lookup table that referenced the different data tables by Identity and a table reference.

如果滚动表以使 Identity 列不会发生冲突，一种可能性是使用通过 Identity 和表引用引用不同数据表的查找表。

Foreign keys on this table would work down the line for referencing tables.

此表上的外键将用于引用表。

This would be expensive in a number of ways Referential integrity on the lookup table would have to be be enforced using triggers. Additional storage of the lookup table and indexing in addition to the data tables. Data reading would almost certainly involve a Stored Procedure or three to execute a filtered UNION. Query plan evaluation would also have a development cost.

这在很多方面都是昂贵的，必须使用触发器来强制执行查找表上的参照完整性。除了数据表之外，查找表和索引的附加存储。数据读取几乎肯定会涉及一个或三个存储过程来执行过滤的 UNION。查询计划评估也会产生开发成本。

The list goes on but it might work on some scenarios.

列表还在继续，但它可能适用于某些情况。

回答by Stupid SQL Tricks

Using Rob Farley's schema:

使用 Rob Farley 的模式：

CREATE TABLE dbo.testtable(
id int IDENTITY(1,1) PRIMARY KEY,
val int NOT NULL); 
go
INSERT dbo.testtable(val)
VALUES(20),(30),(40),(50),(60),(70);
go 
CREATE TABLE dbo.secondtable(
id int NOT NULL,
CONSTRAINT FK_SecondTable FOREIGN KEY(id) REFERENCES dbo.TestTable(id)); 
go

CREATE TABLE z(n tinyint PRIMARY KEY);
INSERT z(n)
VALUES(0),(1);
go
CREATE VIEW dbo.SecondTableCheck WITH SCHEMABINDING AS
SELECT 1 n
FROM dbo.TestTable AS t JOIN dbo.SecondTable AS s ON t.Id = s.Id
CROSS JOIN dbo.z
WHERE t.Val < 50;
go
CREATE UNIQUE CLUSTERED INDEX NoSmallIds ON dbo.SecondTableCheck(n);
go

I had to create a tiny helper table (dbo.z) in order to make this work, because indexed views cannot have self joins, outer joins, subqueries, or derived tables (and TVCs count as derived tables).

我必须创建一个很小的辅助表 (dbo.z) 才能完成这项工作，因为索引视图不能有自联接、外联接、子查询或派生表（并且 TVC 算作派生表）。

回答by Rob Farley

From a purely data integrity perspective (and nothing to do with the Query Optimizer), I had considered an Indexed View. I figured you could make a unique index on it, which could be broken when you try to have broken integrity in your underlying tables.

从纯粹的数据完整性角度（与查询优化器无关），我考虑过索引视图。我认为您可以在其上创建一个唯一索引，当您尝试破坏基础表中的完整性时，该索引可能会被破坏。

But... I don't think you can get around the restrictions of indexed views well enough.

但是...我认为您不能很好地绕过索引视图的限制。

For example:

例如：

You can't use outer joins, or sub-queries. That makes it very hard to find the rows that don't exist in the view. If you use aggregates, you can't use HAVING, so that cuts out some options you could use there too. You can't even have constants in an indexed view if you have grouping (whether or not you use a GROUP BY clause), so you can't even try putting an index on a constant field so that a second row will fall over. You can't use UNION ALL, so the idea of having a count which will break a unique index when it hits a second zero won't work.

您不能使用外部联接或子查询。这使得很难找到视图中不存在的行。如果您使用聚合，则不能使用 HAVING，因此会删除一些您也可以在那里使用的选项。如果您有分组（无论您是否使用 GROUP BY 子句），您甚至不能在索引视图中使用常量，因此您甚至不能尝试在常量字段上放置索引，以便第二行会倒下。您不能使用 UNION ALL，因此在遇到第二个零时会破坏唯一索引的计数的想法是行不通的。

I feel like there should be an answer, but I'm afraid you're going to have to take a good look at your actual design and work out what you really need. Perhaps triggers (and good indexes) on the tables involved, so that any changes that might break something can roll it all that.

我觉得应该有一个答案，但恐怕您将不得不仔细查看您的实际设计并找出您真正需要的东西。也许涉及的表上的触发器（和良好的索引），以便任何可能破坏某些内容的更改都可以滚动它。

But I was really hoping to be able to suggest something that the Query Optimizer might be able to leverage to help the performance of your system, but I don't think I can.

但我真的希望能够提出一些查询优化器可以用来帮助提高系统性能的建议，但我认为我不能。

回答by AaronLS

You could stage the filtered table 1 data to another table. The contents of this staging table are your view 1, and then you build view 2 via a join of the staging table and table 2. This way the proccessing for filtering table 1 is done once and reused for both views.

您可以将过滤后的表 1 数据暂存到另一个表。此临时表的内容是您的视图 1，然后您通过临时表和表 2 的连接构建视图 2。这样，过滤表 1 的处理就完成一次，并为两个视图重复使用。

Really what it boils down to is that view 2 has no idea what kind of filtering you performed in view 1, unless you tell view 2 the filtering criteria, or make it somehow dependent on the results of view 1, which means emulating the same filtering that occurs on view1.

归根结底，视图 2 不知道您在视图 1 中执行了哪种过滤，除非您告诉视图 2 过滤条件，或者使其以某种方式依赖于视图 1 的结果，这意味着模拟相同的过滤发生在 view1 上。

Constraints don't perform any kind of filtering, they only prevent invalid data, or cascade key changes and deletes.

约束不执行任何类型的过滤，它们仅防止无效数据或级联键更改和删除。

回答by AaronLS

Another approach, depending on your requirements, would be to use a stored procedure to return two recordsets. You pass it filtering criteria and it uses the filtering criteria to query table 1, and then those results can be used to filter the query to table 2 so that it's results are also consistent. Then you return both results.

根据您的要求，另一种方法是使用存储过程返回两个记录集。您将过滤条件传递给它，它使用过滤条件查询表 1，然后这些结果可用于将查询过滤到表 2，使其结果也保持一致。然后你返回两个结果。