SQL 检查约束中的子查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13000698/
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
Sub queries in check constraint
提问by Highland
I have table designed in SQL-Server2008 R2.
我有在SQL-Server2008 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))
This cause an error
这会导致错误
Sub-queries are not allowed in this context. Only scalar expressions are allowed.
在这种情况下不允许子查询。只允许使用标量表达式。
I have looked at this question about Check Constraint - Subqueries are not allowed in this context.
我看过这个关于检查约束的问题- 在这种情况下不允许子查询。
Is there any way of achieving this without using a trigger?
有没有办法在不使用触发器的情况下实现这一目标？
回答by Johanna Larsson
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')
回答by Gayanee Wijayasekara
You can't have sub queries inside check constraints. What you can do is use a UDF that returns a scalar value inside the check constraint.
您不能在检查约束内使用子查询。您可以做的是使用在检查约束内返回标量值的 UDF。
Step 1: Create the table
第 1 步：创建表
USE CTBX
GO
CREATE TABLE RawMaterialByGender
(
RMGID int primary key identity(1,1),
RMID smallint foreign key references RawMaterialMaster(RMID),
LeveLMasterID smallint foreign key references LevelMaster(LevelTextID),
IsDeleted bit
)
Step 2 : Create the UDF that returns a scalar
第 2 步：创建返回标量的 UDF
Create FUNCTION [dbo].[IsValidLevelMasterGender](@LevelMasterID smallint)
RETURNS bit
AS
BEGIN
DECLARE @count smallint;
DECLARE @return bit;
SELECT @count = count(LevelTextID)
FROM [LevelMaster]
WHERE LevelCategoryID = 3 AND IsActive = 1 AND LevelTextID=@LevelMasterID
IF(@count = 0)
SET @return = 'false';
ELSE
SET @return = 'true';
RETURN @return;
END;
GO
Step 3 : Alter table to add the CHECK constraint
第 3 步：更改表以添加 CHECK 约束
ALTER TABLE RawMaterialByGender
ADD CONSTRAINT check_LevelMasterID CHECK (dbo.IsValidLevelMasterGender(LeveLMasterID) = 'true')
回答by shahkalpesh
ALTER TABLE Table1
ADD CONSTRAINT FK_Table1_Code FOREIGN KEY (MyField)
REFERENCES Table2 (Field) ;
Ref: http://msdn.microsoft.com/en-us/library/ms190273.aspx
Note: I haven't checked the above for syntax.
参考：http: //msdn.microsoft.com/en-us/library/ms190273.aspx
注意：我没有检查上面的语法。
回答by Lukasz Szozda
First of all in your example you clearly need FK Constraint.
首先，在您的示例中，您显然需要 FK 约束。
Another posibility is to use view with WITH CHECK OPTIONand give access to user through it:
另一种可能性是使用 viewWITH CHECK OPTION并通过它授予用户访问权限：
CREATE TABLE Table1(i INT PRIMARY KEY, CK_Code CHAR(1));
CREATE TABLE Table2(Field CHAR(1));
INSERT INTO Table2(Field) VALUES ('A'),('B'), ('C');
GO
CREATE VIEW v_Table1
AS
SELECT *
FROM Table1
WHERE CK_code IN (SELECT Field FROM Table2) -- here goes your subquery check
WITH CHECK OPTION;
When you try to insert data that violates your "constraint" like:
当您尝试插入违反“约束”的数据时，例如：
INSERT INTO v_Table1(i, CK_Code)
VALUES(10, 'D');
You will get:
你会得到：
The attempted insert or update failed because the target view either specifies WITH CHECK OPTION or spans a view that specifies WITH CHECK OPTION and one or more rows resulting from the operation did not qualify under the CHECK OPTION constraint.
The statement has been terminated.
尝试插入或更新失败，因为目标视图要么指定了 WITH CHECK OPTION，要么跨越了一个指定了 WITH CHECK OPTION 的视图，并且该操作产生的一个或多个行不符合 CHECK OPTION 约束条件。
该语句已终止。
