MS SQL Server cross table constraint

问题:

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?

回答1:

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)

回答2:

It's not a great solution, but you could add triggers to screen and relation tables which just check what you've modified meets your criteria, and rollback if not.

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