检查约束中的子查询 [英] Sub queries in check constraint

问题描述

我有在SQL-Server 2008 R2中设计的表.

I have table designed in SQL-Server 2008 R2.

我在该表中有一列，在插入数据时需要对照另一张表进行检查.

I have a column in that table which needs to be checked against another table when data is inserting.

ALTER TABLE Table1
        WITH CHECK ADD CONSTRAINT CK_Code
        CHECK (MyField in (Select Field From Table2))

这会导致错误

在这种情况下，不允许

子查询.只能使用标量表达式.

Sub-queries are not allowed in this context. Only scalar expressions are allowed.

我查看了有关检查约束-子查询的问题在这种情况下是不允许的.

有没有不用触发器就可以实现这一目标的方法?

Is there any way of achieving this without using a trigger?

推荐答案

注意，您真正想要的是外键约束.也就是说，要使查询"进入检查，您可以编写一个包含查询并输出标量值的函数，然后在检查约束中使用该函数.

Note, what you really want is a foreign key constraint. That said, to get a "query" into a check you can write a function that contains the query and outputs a scalar value, then use that function in the check constraint.

CREATE FUNCTION myFunction (
    @field DATATYPE(?)
)
RETURNS VARCHAR(5)
AS
BEGIN
    IF EXISTS (SELECT* FROM Table2 WHERE MYFIELD = @field)
        return 'True'
    return 'False'
END

类似的东西.未经测试.

Something like that. Not tested.

然后您可以像这样将其添加到您的支票中

Then you can add it to your check like so

ALTER TABLE Table1
    WITH CHECK ADD CONSTRAINT CK_Code
    CHECK (myFunction(MYFIELD) = 'True')

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