MS SQL Server 交叉表约束 [英] MS SQL Server cross table constraint

问题描述

我有三张桌子:

1) 应用程序 (AppId, Name)
2) 屏幕 (ScreenId, Name)
3)关系(AppId、ScreenId)

1) Applications (AppId, Name)
2) Screen (ScreenId, Name)
3) Relation (AppId, ScreenId)

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

Now I want to apply some restrictions on related table: The same screen can be assigned to multiple application, but there cannot be two screens with same name that assigned to same application.

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

I know I can add Screen.Name to relation table and then create PK on AppId and Screen.Name, but I don't want such solution, since Screen.Name is subject to change.

为了达到这样的限制，我还有哪些额外的选择?

What additional options I have to achieve such restriction?

推荐答案

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

You can create an indexed view based on the Relation and Screen tables and apply a unique constraint there.

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)

这篇关于MS SQL Server 交叉表约束的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！