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.

+1


source to share


1 answer


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) 

      

+2


source







All Articles