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