MS SQL Server cross table constraint

Question

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?

score 12 · Accepted Answer · answered May 01 '13 at 07:39

12

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)

answered May 01 '13 at 07:39

Damien_The_Unbeliever

234,701
27
340
448

I wondered if there was something like this. :) Much better than triggers. :) – Jonathan May 01 '13 at 07:46
1

Will this method "validate" the data when changing the screen name, or it will be check the data only when accessing view? – Alex Dn May 01 '13 at 07:59
2

@AlexDn - yes, this will enforce the constraint when changes are made in the underlying tables. If you go to the page I linked to, you'll notice that there are a lot of restrictions on indexed views. Most of these restrictions exist *because* they allow the required maintenance activity to occur on each transaction in the base tables in an efficient manner. – Damien_The_Unbeliever May 01 '13 at 08:09
2

@AlexDn - also, you may have noticed that I named the view `DRI_x`. This is (my own) convention for indicating that this view exists for Declarative Referential Integrity reasons, and is (probably) not intended for anyone to ever actually query from it. This helps if you're in an environment where audits take place - "what's the point of this view? Nothing queries it. Maybe we should delete it?" – Damien_The_Unbeliever May 01 '13 at 08:14
1

I love indexed views but it feels like I only satisfy all the many restrictions about 20% of the times I want to use them; the other 80% I have to resort to triggers... – AakashM May 01 '13 at 08:16

Jonathan · Answer 2 · 2013-05-01T07:45:25.427

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

Yes, triggers will work to, but I'm trying avoid triggers as much as possible. — Alex Dn, May 01 '13 at 08:00

MS SQL Server cross table constraint

2 Answers2

Linked