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?

Damien_The_Unbeliever · Accepted Answer · 2013-05-01 07:39:09Z

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 1, 2013 at 7:39

Damien_The_Unbeliever

241k28 gold badges358 silver badges470 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Jonathan Over a year ago

I wondered if there was something like this. :) Much better than triggers. :)

Alex Dn Over a year ago

Will this method "validate" the data when changing the screen name, or it will be check the data only when accessing view?

Damien_The_Unbeliever Over a year ago

@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 Over a year ago

@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?"

AakashM Over a year ago

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...

Jonathan · Accepted Answer · 2013-05-01 07:45:25Z

0

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

edited May 1, 2013 at 7:45

answered May 1, 2013 at 7:37

Jonathan

26.8k13 gold badges69 silver badges85 bronze badges

1 Comment

Alex Dn Over a year ago

Yes, triggers will work to, but I'm trying avoid triggers as much as possible.

Collectives™ on Stack Overflow

MS SQL Server cross table constraint

2 Answers 2

5 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related