MS SQL Server交叉表约束

Question

我有三张表:

1) 应用程序 (AppId, 名称) 2) 屏幕 (ScreenId, 名称) 3) 关联 (AppId, ScreenId)

现在我想对相关表应用一些限制: 同一个屏幕可以分配给多个应用程序，但不能有两个屏幕具有相同的名称，并分配给同一个应用程序。

我知道我可以将Screen.Name添加到关系表中，然后在AppId和Screen.Name上创建PK，但我不想要这样的解决方案，因为Screen.Name可能会更改。

我还有哪些其他选项来实现这种限制呢？

- Alex Dn

Answer 1

您可以基于Relation和Screen表创建一个索引视图，并在那里应用唯一约束。

create view DRI_UniqueScreens
with SCHEMABINDING
as
    select r.AppId,s.Name
    from
       [Schema].Relation r
         inner join
       [Schema].Screen s
         on
            r.ScreenId = s.ScreenId
GO
CREATE UNIQUE CLUSTERED INDEX IX_DRI_UniqueScreens
    on DRI_UniqueScreens (AppId,Name)

- Damien_The_Unbeliever

---

我想知道是否有类似的东西。 :) 比触发器好多了。 :) - Jonathan

这个方法在更改屏幕名称时会“验证”数据吗，还是只有在访问视图时才会检查数据？ - Alex Dn

@AlexDn - 是的，这将在基础表中进行更改时强制执行约束。如果您转到我链接的页面，您会注意到对索引视图有很多限制。大多数这些限制存在是因为它们允许在基本表中的每个事务中以高效的方式进行所需的维护活动。 - Damien_The_Unbeliever

@AlexDn - 另外，您可能已经注意到我将视图命名为DRI_x。这是（我的）约定，用于指示此视图存在于声明性引用完整性原因，并且（可能）不打算供任何人实际查询。如果您处于进行审计的环境中，则可以使用此功能 - “这个视图有什么意义？没有任何查询它。也许我们应该删除它？” - Damien_The_Unbeliever

我喜欢索引视图，但感觉只有大约20%的时间满足所有限制条件才能使用它们；另外80%的时间我不得不使用触发器... - AakashM

Answer 2

这不是一个很好的解决方案，但你可以在屏幕和关系表中添加触发器，仅检查你修改的内容是否符合你的标准，如果不符合就回滚。

CREATE TRIGGER trgScreen ON Screen FOR INSERT, UPDATE
AS
BEGIN
    IF EXISTS (SELECT r.AppID, s.Name FROM Screen s
               INNER JOIN Relation r ON s.ScreenID = r.ScreenID
               GROUP BY r.AppID, s.Name
               HAVING count(*) > 1)
        ROLLBACK TRANSACTION
END

CREATE TRIGGER trgRelation ON Relation FOR INSERT, UPDATE
AS
BEGIN
    IF EXISTS (SELECT r.AppID, s.Name FROM Screen s
               INNER JOIN Relation r ON s.ScreenID = r.ScreenID
               GROUP BY r.AppID, s.Name
               HAVING count(*) > 1)
        ROLLBACK TRANSACTION
END

- Jonathan

---

是的，触发器也可以使用，但我尽可能地想避免使用触发器。 - Alex Dn