I have a database with the following tables
I need to ensure that users only becomes friends with other users of the same department.
Approach 1 – DepartmentId on Friend
On http://stackoverflow.com/a/20443626/263003, diarmuid suggested to add the DepartmentId on the Friend table and use the foreign key constraints of (MyselfId, AppId) and (FriendId, AppId). However, I would have to set the primary key of the User table as (Id, DepartmentId) – this creates a super key and I want to avoid that.
Approach 2 – CHECK CONSTRAINT on VIEW
The simplest approach I could think of is to create a VIEW joining User to Friend to User, and add a CHECK CONSTRAINT on the view. However, CHECK CONSTRAINT only applies on TABLE and not VIEW.
Approach 3 – CROSS JOIN and UNIQUE INDEX on VIEW
On http://stackoverflow.com/a/9751172/263003, Martin Smith uses the approach of selecting the records we do not want, creating duplicate records of those using CROSS JOIN, and ensuring those records cannot exists by using a UNIQUE INDEX. Let’s start by creating the INDEXED VIEW
CREATE VIEW [dbo].[CheckFriendView]
WITH SCHEMABINDING
AS
SELECT myself.Id AS MyselfId, myFriend.Id AS MyFriendId, myself.DepartmentId AS MyDepartmentId, myFriend.DepartmentId AS FriendDepartmentId
FROM dbo.[User] AS myself
INNER JOIN dbo.Friend
ON dbo.Friend.MyselfId = myself.Id
INNER JOIN dbo.[User] AS myFriend
ON dbo.Friend.FriendId = myFriend.Id
WHERE myself.DepartmentId <> myFriend.DepartmentId
GO
CREATE UNIQUE CLUSTERED INDEX IDX_CheckFriendView
ON [dbo].[CheckFriendView] (MyselfId, MyFriendId)
GO
However, I got the error
Cannot create index on view "Demo.dbo.CheckFriendView". The view contains a self join on "Demo.dbo.User".
Let’s avoid self join by moving the join to a WHERE clause
CREATE VIEW [dbo].[CheckFriendView]
WITH SCHEMABINDING
AS
SELECT MyselfId, FriendId
FROM dbo.Friend AS f
WHERE EXISTS (
SELECT 1
FROM dbo.[User] AS myself
INNER JOIN dbo.Friend
ON dbo.Friend.MyselfId = myself.Id
INNER JOIN dbo.[User] AS myFriend
ON dbo.Friend.FriendId = myFriend.Id
WHERE myself.Id = f.MyselfId AND
myFriend.Id = f.FriendId AND
myself.DepartmentId <> myFriend.DepartmentId
)
GO
CREATE UNIQUE CLUSTERED INDEX IDX_CheckFriendView
ON [dbo].[CheckFriendView] (MyselfId, FriendId)
GO
Another error comes up
Cannot create index on view "Demo.dbo.CheckFriendView" because it contains one or more subqueries. Consider changing the view to use only joins instead of subqueries. Alternatively, consider not indexing this view.
Looks like SQL Server will never let us join.
Approach 4 – Turning MyselfId and FriendId into a single column
Jonathan avoided the self join by combining the MyselfId and FriendId columns with the help of a numbers table containing 2 entries, then joining into the User table – http://jmkehayias.blogspot.sg/2008/12/creating-indexed-view-with-self-join.html
Let’s start by combining MyselfId and FriendId
CREATE VIEW [dbo].[CheckFriendOneColumnView]
WITH SCHEMABINDING
AS
SELECT MyselfId, CASE WHEN n.Number = 1 THEN MyselfId ELSE FriendId END AS MyselfOrFriendId
FROM dbo.Friend AS f
CROSS JOIN dbo.[Number] AS n
And then add the joining of User
INNER JOIN dbo.[User] AS u
ON f.UserId = u.Id OR f.FriendId = u.Id
Oh wait a minute, this is getting unnecessarily complicated. Could we just use
INNER JOIN dbo.[User] AS u
ON f.UserId = u.Id OR f.FriendId = u.Id
without the cross join?
Finally
We do an INNER JOIN with OR condition. Each row in Friend will become 2 rows, one with the DepartmentId for myself, the other with the DepartmentId for my friend.
Then aggregate the (MyselfId, DepartmentId). Since all the friends should belong to the same department, this aggregation should leave MyselfId as unique.
Lastly add the UNIQUE INDEX on MyselfId.
CREATE VIEW [dbo].[CheckFriendView]
WITH SCHEMABINDING
AS
SELECT f.MyselfId, u.DepartmentId, COUNT_BIG(*) AS BigCount
FROM dbo.Friend AS f
INNER JOIN dbo.[User] AS u
ON f.MyselfId = u.Id OR f.FriendId = u.Id
GROUP BY f.MyselfId, u.DepartmentId
GO
CREATE UNIQUE CLUSTERED INDEX IDX_CheckFriendView
ON [dbo].[CheckFriendView] (MyselfId)
GO
Note: I had to add COUNT_BIG(*) AS BigCount due to another error
Cannot create index on view 'CheckFriendView' because its select list does not include a proper use of COUNT_BIG. Consider adding COUNT_BIG(*) to select list.
which basically says I must add it!
Update:
Warning: while the INNER JOIN ON a OR b works, it results in very inefficient query (no JOIN predicate) and slows down every changes in the database!