Limit values in a table using column values in another table
So, I have two tables:
Requests
--------
Id
RequestSchemeId
ReceivedByUserId
ForwardedRequests
-----------------
Id
RequestId (FK to Id column of Requests Table)
ForwardedToUserId
Now, one business rule says that a user can only receive one request for a specific RequestScheme. So I created a UniqueKey constraint for RequestSchemeId + ReceivedByUserId. Which should solve my problem
The second business rule is that a request can only be redirected to another user if the forwarded user has not yet redirected a request in the same manner from any other user.
The solution I can think of is the RequestSchemeId column in the ForwardedRequests table, which is just a copy of the value in the corresponding row of the Requests table, and then adds a unique constraint on ForwardedToUserId + RequestSchemeId.
Is this the correct way to do it? If not, what?
How do I get the RequestSchemeId value from requests in ForwardedRequests when a new row is created in the last one? UDF is what I was looking at, but it seemed to have some finishing touches and I needed a standard / recommended way of doing it not something sloppy.
source to share
You can use a unique index on the view to enforce this constraint. See example below.
CREATE TABLE dbo.Requests
(
Id INT PRIMARY KEY,
RequestSchemeId INT,
ReceivedByUserId INT,
UNIQUE (RequestSchemeId, ReceivedByUserId)
)
CREATE TABLE dbo.ForwardedRequests
(
Id INT PRIMARY KEY,
RequestId INT REFERENCES Requests(Id),
ForwardedToUserId INT
)
GO
CREATE VIEW dbo.ForwardedUserRequestSchemes
WITH SCHEMABINDING
AS
SELECT ForwardedToUserId,
RequestSchemeId
FROM dbo.ForwardedRequests FR
JOIN dbo.Requests R
ON R.Id = FR.RequestId
GO
CREATE UNIQUE CLUSTERED INDEX ix
ON dbo.ForwardedUserRequestSchemes(ForwardedToUserId, RequestSchemeId)
source to share