使用另一个表中的列的值约束表中的值

2021-09-10 00:00:00 sql tsql sql-server-2008 sql-server

所以我有两个表:

Requests
--------
Id
RequestSchemeId
ReceivedByUserId

ForwardedRequests
-----------------
Id
RequestId (FK to Id column of Requests Table)
ForwardedToUserId

现在有一个业务规则规定，用户只能收到一个针对特定 RequestScheme 的请求.所以我为 RequestSchemeId+ReceivedByUserId 创建了一个 UniqueKey 约束.哪个应该可以解决我的问题

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.

我能想到的解决方案是 ForwardedRequests 表中的 RequestSchemeId 列，它只是 Requests 表相关行中值的副本，然后在 ForwardedToUserId+RequestSchemeId 上添加唯一约束.

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?

当在后者中创建新行时，如何从 Requests 到 ForwardedRequests 获取 RequestSchemeId 的值?UDF 是我看过的东西，但它似乎有一些问题，我需要标准/推荐的方法来做到这一点而不是片状.

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.

推荐答案

您可以在视图上使用唯一索引来强制执行此约束.示例如下.

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)

相关文章