SQL Check 约束可以与另一个表相关吗？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3880698/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Can a Check constraint relate to another table?
提问by Hyman Johnstone
Let's say I have one table called ProjectTimeSpan(which I haven't, just as an example!) containing the columns StartDateand EndDate.
假设我有一个名为ProjectTimeSpan 的表（我没有，只是作为一个例子！）包含列StartDate和EndDate。
And that I have another table called SubProjectTimeSpan, also containing columns called StartDateand EndDate, where I would like to set a Check constraintthat makes it impossible to set StartDate and EndDate to values "outside" the ProjectTimeSpan.StartDateto ProjectTimeSpan.EndDate
而且我有另一个名为SubProjectTimeSpan 的表，也包含名为StartDate和EndDate 的列，我想在其中设置一个Check 约束，这使得无法将 StartDate 和 EndDate 设置为ProjectTimeSpan.StartDate到ProjectTimeSpan.EndDate“外部”的值
Kind of a Check constraint that knowsabout anothertables values...
一个检查约束的种类知道关于另一个表中的值...
Is this possible?
这可能吗？
回答by Andomar
In response to your comment on GSerg's answer, here's an example check constraint using a function:
为了回应您对 GSerg 的回答的评论，这里有一个使用函数的示例检查约束：
alter table YourTable
add constraint chk_CheckFunction
check (dbo.CheckFunction() = 1)
Where you can define the function like:
您可以在哪里定义函数，例如：
create function dbo.CheckFunction()
returns int
as begin
return (select 1)
end
The function is allowed to reference other tables.
该函数允许引用其他表。
回答by GSerg
You can create a user-defined functionthat does the check and returns 1 or 0, then create a checkconstraint on it, providing project id and the dates as the parameters.
您可以创建一个用户定义的函数来执行检查并返回 1 或 0，然后对其创建check约束，提供项目 ID 和日期作为参数。
回答by onedaywhen
Make a compound key of the ProjectTimeSpantable's key combined with the StartDateand EndDatecolumns, then use this compound key for your foreign key reference in your SubProjectTimeSpantable. This will give you the ability to write the necessary row-level CHECKconstraints in the SubProjectTimeSpantable e.g.
将ProjectTimeSpan表的键与StartDate和EndDate列组合成一个复合键，然后将此复合键用作SubProjectTimeSpan表中的外键引用。这将使您能够CHECK在SubProjectTimeSpan表中编写必要的行级约束，例如
CREATE TABLE ProjectTimeSpan
(
project_ID INTEGER NOT NULL UNIQUE, -- key
StartDate DATE NOT NULL,
EndDate DATE NOT NULL,
CHECK (StartDate < EndDate),
UNIQUE (project_ID, StartDate, EndDate) -- compound key
-- other project columns here...
);
CREATE TABLE SubProjectTimeSpan
(
project_ID INTEGER NOT NULL,
StartDate DATE NOT NULL,
EndDate DATE NOT NULL,
FOREIGN KEY (project_ID, StartDate, EndDate)
REFERENCES ProjectTimeSpan (project_ID, StartDate, EndDate)
ON DELETE CASCADE
ON UPDATE CASCADE,
sub_StartDate DATE NOT NULL,
sub_EndDate DATE NOT NULL,
CHECK (sub_StartDate < sub_EndDate),
CHECK (StartDate <= sub_StartDate), -- sub project can't start before main project
CHECK (sub_EndDate <= EndDate) -- sub project can't end after main project
-- other sub project columns here...
);
回答by Marco Guignard
You need to add constraint on the parent and the children table because the subproject can't be out of the project range but the project range can't move out of all the subproject too.
您需要在父表和子表上添加约束，因为子项目不能超出项目范围，但项目范围也不能移出所有子项目。
In these kind of situations, you should defer the check of the constraint on an upper level (webservice, application) with a transaction to ensure your data are in a valid state after multiple query on both table !
在这种情况下，您应该通过事务推迟对上层（Web 服务、应用程序）的约束检查，以确保在对两个表进行多次查询后您的数据处于有效状态！
回答by DharmaTurtle
You certainly cando this as many answers have shown. However, you should be aware that SQL Server seems to have trouble with CHECK CONSTRAINTs that use UDFs:
正如许多答案所示，您当然可以这样做。但是，您应该知道 SQL Server 似乎对CHECK CONSTRAINT使用 UDF 的 s有问题：
https://dba.stackexchange.com/questions/12779/how-are-my-sql-server-constraints-being-bypassed
https://dba.stackexchange.com/questions/12779/how-are-my-sql-server-constraints-being-bypassed
