MS SQL Server cross table constraint

sql, sql-server, t-sql

Solution

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)

Problem

I have three tables: 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. 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?

Original source