如何执行包含多个表的唯一性规则？ [英] How can I enforce uniqueness rules that include more than one table?

问题描述

例如，具有以下表格：

Companies (CompanyID)
Workers (WorkerID, CompanyID)
Users (WorkerID, Username)

定义正确的关系，我如何强制执行用户的用户名在其员工在的公司中是唯一的？

With the correct relationships defined, how do I enforce the predication that a User's Username is unique in the company where its worker is in?

推荐答案

您可以使用索引视图

CREATE VIEW dbo.EnforceConstraint
WITH SCHEMABINDING
AS
SELECT CompanyID, Username
FROM dbo.Users u 
    JOIN dbo.Workers w ON w.WorkerID = u.WorkerID

GO

CREATE UNIQUE CLUSTERED INDEX ix ON dbo.EnforceConstraint(CompanyID, Username)

有一定的 SET 需要修改索引视图的基表的选项，但如果您在SQL Server 2005+上，这些选项将默认启用。

There are certain SET options that need to be on for modification of the base tables of indexed views but if you are on SQL Server 2005+ these are on by default.

这篇关于如何执行包含多个表的唯一性规则？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！