All geek questions in one place

MS SQL Server crosstab limit

I have three tables:

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

Now I want to apply some restrictions for a linked table: The same screen can be assigned to several applications, but there can not be two screens with the same name that are assigned to one application.

I know that I can add Screen.Name to the relationship table, and then create a PK on AppId and Screen.Name, but I do not want such a solution, since Screen.Name can be changed.

What additional features should I achieve such a limitation?

+6
source share
2 answers

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)
+8
source

This is not a great solution, but you can add triggers for screen tables and relationships that simply check that you have changed, match your criteria and rollbacks 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
0
source

Source: https://habr.com/ru/post/944022/

More articles:


All Articles