So I have two tables:
Requests
--------
Id
RequestSchemeId
ReceivedByUserId
ForwardedRequests
-----------------
Id
RequestId (FK to Id column of Requests Table)
ForwardedToUserId
and one view
ForwardedRequestsInRequestSchemes
---------------------------------
Requests.RequestSchemeId
Requests.ReceivedByUserId
ForwardedRequests.ForwardedToUserId
What's the standard/recommended way of adding a constraint equivalent to Requests.ReceivedByUserId != ForwardedRequests.ForwardedToUserId
in the view?
I know check constraints are not allowed in views. Using SQL Server 2008.
EDIT:
This is a followup question to this question.
Business rules:
The same request can be forwarded to multiple users. Hence the Id column in the ForwardedRequests table.
A user can receive only one Request for a particular RequestScheme. So I created a UniqueKey constraint for RequestSchemeId+ReceivedByUserId in the Requests table.
The request can be forwarded to another user only if forwarded user does not already have a forwarded request under the same scheme from any other user. So as Martin suggested in the linked question, I created a view from the two tables and added a unique constraint on Requests.RequestSchemeId+ForwardedRequests.ForwardedToUserId.
The business rule this question is about, is that the receiver of the request cannot forward it to himself/herself.
I can think of a couple of ways of getting SQL Server to enforce this for you. Both pretty hacky though so interested to see any other approaches.
1) You could add to the indexed view ForwardedRequestsInRequestSchemes
an additional column 1/(ForwardedToUserId - ReceivedByUserId) AS FailIfSame
which would raise a Divide by zero error
if the two values are the same. This does mean that you end up storing a redundant column in the indexed view though.
2) You could create a new view that returns any such rows cross joined onto a two row table then define a unique constraint on 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)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With