An alternative to checking constraints in views
So, I have two tables:
Requests
--------
Id
RequestSchemeId
ReceivedByUserId
ForwardedRequests
-----------------
Id
RequestId (FK to Id column of Requests Table)
ForwardedToUserId
and one kind
ForwardedRequestsInRequestSchemes
---------------------------------
Requests.RequestSchemeId
Requests.ReceivedByUserId
ForwardedRequests.ForwardedToUserId
What's the standard / recommended way to add a constraint equivalent Requests.ReceivedByUserId != ForwardedRequests.ForwardedToUserId
in a view?
I know that validation constraints are not valid in views. Using SQL Server 2008.
EDIT:
This is the next question of this question .
Business rules:
-
The same request can be redirected to multiple users. Hence the Id column in the ForwardedRequests table.
-
A user can only receive one request for a specific RequestScheme. So I created a UniqueKey constraint for RequestSchemeId + ReceivedByUserId in the request table.
-
The request can be redirected to another user only if the forwarded user has not yet redirected the request using the same scheme from any other user. As Martin suggested in the linked question , I created a view from two tables and added a unique constraint on Requests.RequestSchemeId + ForwardedRequests.ForwardedToUserId.
-
The business rule in question is that the recipient of a request cannot transmit it to themselves.
source to share
I can think of a couple of ways to get SQL Server to provide this for you. Both are pretty hacky, although interested to see any other approaches.
1) You can add ForwardedRequestsInRequestSchemes
an extra column to the indexed view 1/(ForwardedToUserId - ReceivedByUserId) AS FailIfSame
that will raise the value Divide by zero error
if the two values are the same. This means that you end up storing the redundant column in the indexed view.
2) You can create a new view that returns any such cross rows, concatenated into a table of two rows, and then define a unique constraint for that view. This view will always be empty.
CREATE TABLE dbo.TwoRows(C INT) INSERT INTO dbo.TwoRows VALUES(1),(1)
GO
CREATE VIEW dbo.FailIfForwardedUserEqualToReceivedByUser
WITH SCHEMABINDING
AS
SELECT 1 AS C
FROM dbo.ForwardedRequests FR
INNER JOIN dbo.Requests R
ON R.Id = FR.RequestId AND R.ReceivedByUserId = FR.ForwardedToUserId
CROSS JOIN dbo.TwoRows
GO
CREATE UNIQUE CLUSTERED INDEX ix ON
dbo.FailIfForwardedUserEqualToReceivedByUser(C)
source to share
One way is to deny rights update, insert, delete
to tables and enforce business requirements using a stored procedure. For example,
create procedure dbo.AddRequestForward(
@requestId int
, @forwardedToUserId int)
as
insert ForwardedRequests
(ForwardedRequests, ForwardedRequests)
select @requestId
, @forwardedToUserId
where not exists
(
select *
from Requests
where Id = @requestId
and @forwardedToUserId = @forwardedToUserId
)
if @@rowcount = 0
return -1 -- Forwarded and Received identical user
return 1 -- Success
go
source to share