CODEWITHSUNDEEP
sqlsql-serversql-server-2008t-sql
NVM
NVM

Reputation: 5552

Constrain values in a table using values of a column 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 receive only one Request for a particular RequestScheme. So I created a UniqueKey constraint for RequestSchemeId+ReceivedByUserId. Which should solve my problem

The second business rule is that 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.

The solution I can think of is a RequestSchemeId column in the ForwardedRequests table which is just a copy of the value in the related row of the Requests table and then add a unique constraint on ForwardedToUserId+RequestSchemeId.

Is this the correct way of doing it? If not what is?

How do I get value of the RequestSchemeId from Requests to ForwardedRequests when a new row is created in the latter? UDF is something I looked at but it seemed to have some gotchas and I need the standard/recommended way of doing this not something flaky.

Upvotes: 1

Views: 234

Answers (1)

Martin Smith
Martin Smith

Reputation: 453287

You can use a unique index on a view to enforce this constraint. 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)

Upvotes: 2

Related Questions