SQL：如何使用约束强制功能依赖关系？

Question

假设我们有一张表格：

CREATE TABLE Jobs
    (
      JobID INT PRIMARY KEY ,
      AssignedUser VARCHAR(10) ,
      Zone VARCHAR(10)
    )

我们需要执行的约束条件是：确保没有用户被分配到不止一个区域，也就是说，存在一个功能依赖 AssignedUser => Zone。

如何在SQL中执行此操作？不幸的是，这是一个遗留表格，我们无法更改其结构，但我们可以创建约束来强制执行完整性。

- Saintali

---

分配给用户的唯一索引怎么样？它们应该只在表中出现一次，对吧？ - xQbert

不行。一个用户可以被分配多个“工作”，但它们都应该在同一区域内。 - Saintali

在更新/插入之前，触发器可以在过程中验证功能依赖关系。该触发器不容易被绕过，并且该过程可以引发错误以防止重复输入。我无法看到如何仅使用索引来实现这一点，例如http://stackoverflow.com/questions/1631070/looking-for-sql-constraint-select-count-from-tboss-2。 - xQbert

你使用的是哪种关系型数据库管理系统？在SQL Server中，你可以通过创建索引视图来实现这一点。 - Martin Smith

@MartinSmith 这是MS SQL Server 2012。索引视图是什么？ - Saintali

在使用用户定义函数时添加约束。 - xQbert

Answer 1

以下内容应该可行。根据所需的语义，您可能希望添加一个where子句来排除Zone IS NULL。

CREATE VIEW V 
WITH SCHEMABINDING 
AS 
  SELECT AssignedUser, 
         Zone, 
         COUNT_BIG(*) AS C 
  FROM   dbo.Jobs 
  GROUP  BY AssignedUser, 
            Zone 

GO 

CREATE UNIQUE CLUSTERED INDEX IX 
  ON V(AssignedUser) 

- Martin Smith

---

@Saintali 这是由于索引的原因。当基础数据发生更改并且唯一性条件得到检查时，索引将得到维护。 - Martin Smith

这不是某种安全漏洞吗？为什么我可以在_你的_表上定义一个索引视图？ - Saintali

您需要适当的权限。我想可能是对表格的“引用”权限。 - Martin Smith

这是一个多么巧妙的技巧啊！我怎么以前从没见过呢？！ - usr

@Saintali 索引视图有许多限制，包括“Distinct”（去重）。不幸的是。 - Martin Smith