检查约束，确保tableA的列中的值小于tableB的列中的值 [英] Check constraints that ensures the values in a column of tableA is less the values in a column of tableB

问题描述

我正在创建一个数据库，我有表ProjectDetails.TimeCardExp和ProjectDetails.Project。 TimeCardExp表有一列ExpenseDate，在表Project中有列EndDate; （它们都接受datetime数据类型）。我想要做的是在TimeCardExp表的ExpenseDate列上实现一个约束，确保在表TimeCardExp的ExpenseDate列中输入的值小于同一个员工的Project表的EndDate列中的值。

TimeCardExp表* TimeCardID * EmployeeID * ProjectID * ExpenseDate

项目表* ProjectID * EmployeeID * EndDate

上面是两个表和一些列，我的任务是确保在TimeCard.ExpenseDate中输入的值总是小于同一个员工的值Project.EndDate。感谢

解决方案

如果可以表达你想要避免的条件作为一个简单的连接），那么你可以使用索引视图以声明的方式强制执行这个操作，只要创建一个帮助器表。

  CREATE TABLE dbo.TwoRows 
（
 X INT PRIMARY KEY 
）; 
 
 INSERT INTO dbo.TwoRows 
 VALUES（1），（2）
  

$ b b

然后，您只需表达不应该发生的条件，并将其交叉连接到帮助程序表。

  CREATE VIEW TimeCardExpenseDateLessThanProjectEndDate 
 WITH SCHEMABINDING 
 AS 
 SELECT P.ProjectID，
 P.EmployeeID，
 P.EndDate，
 TC.ExpenseDate 
 FROM ProjectDetails.Project AS P 
 INNER JOIN ProjectDetails.TimeCardExp AS TC 
 ON P.ProjectID = TC.ProjectID 
 AND P.EmployeeID = TC.EmployeeID 
 AND P.EndDate< = TC.ExpenseDate 
 CROSS JOIN dbo.TwoRows 
 
 GO 
 
 CREATE UNIQUE CLUSTERED INDEX IX 
 ON TimeCardExpenseDateLessThanProjectEndDate（ProjectID，
 EmployeeID，
 EndDate，
 ExpenseDate）
  pre> 
I'm creating a database and i have the tables ProjectDetails.TimeCardExp and ProjectDetails.Project. The TimeCardExp table has a column ExpenseDate, and in the table Project, there is column EndDate; (they both accept the datetime data type). What I want to do is to implement a constraint on the ExpenseDate column of the TimeCardExp table that ensures that the values entered in the ExpenseDate column of table TimeCardExp are less than the values in the EndDate column of the Project table, for the same employee.

TimeCardExp table *TimeCardID*EmployeeID*ProjectID*ExpenseDate    

Project table  *ProjectID*EmployeeID*EndDate

Above is the two tables and some columns, my task is to make sure values entered in TimeCard.ExpenseDate are always less than the values Project.EndDate, for the same employee. thanks 
解决方案
If it is possible to express the condition you want to avoid as a simple join (as seems to be the case here) then you can enforce this declaratively with an indexed view as long as you create a helper table.
CREATE TABLE dbo.TwoRows
  (
     X INT PRIMARY KEY
  );

INSERT INTO dbo.TwoRows
VALUES      (1), (2)
Then you just express the condition that should never occur and cross join that to the helper table. 
CREATE VIEW TimeCardExpenseDateLessThanProjectEndDate
WITH SCHEMABINDING
AS
  SELECT P.ProjectID,
         P.EmployeeID,
         P.EndDate,
         TC.ExpenseDate
  FROM   ProjectDetails.Project AS P
         INNER JOIN ProjectDetails.TimeCardExp AS TC
           ON P.ProjectID = TC.ProjectID
              AND P.EmployeeID = TC.EmployeeID
              AND P.EndDate <= TC.ExpenseDate
         CROSS JOIN dbo.TwoRows

GO

CREATE UNIQUE CLUSTERED INDEX IX
  ON TimeCardExpenseDateLessThanProjectEndDate(ProjectID, 
                                               EmployeeID, 
                                               EndDate, 
                                               ExpenseDate) 


                        
这篇关于检查约束，确保tableA的列中的值小于tableB的列中的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！