将来自另一个表的数据放入检查约束中

问题描述

我有表说table1与colum说checkColumn，

现在我想把一个检查约束到字段checkColumn，这将只允许在那里的数据另一个表说某些条件的表2，

我试过这样，

  ALTER TABLE table1 
 ADD CHECK（checkColumn =（从table2选择field1，其中field2 ='ABC'））//选择不是标量
  

但是我意识到它不允许子查询字符串在检查条件，我搜索一点，读我应该使用外键，或触发器，或

感谢

解决方案

不幸的是，您不能将Sub Query插入到Check约束的上下文中。但这里我想给出建议，可以使用任何触发器或函数，可以使用
外键约束来检查数据依赖性
我想与函数共享一个示例。
eg

  CREATE FUNCTION fn_Check_Rollnumber（
 @Rollnumber INT 
）
 RETURNS VARCHAR（10）
 AS 
 BEGIN 
如果存在（SELECT Rollnumber FROM Table_Student WHERE Rollnumber = @Rollnumber）
 return'True'
 return'False'
 END 
  

现在你可以使用这个函数检查上下文，例如

ALTER TABLE Table_Fees
检查添加约束CK_RollCheck
CHECK（fn_Check_Rollnumber（Rollnumber）='True'）

I have table say table1 with a colum say checkColumn,

now I want to put a check constraint to the field checkColumn which would allow only data which is there in another table say table 2 for some conditions,

I tried this like this,

ALTER TABLE table1
ADD CHECK (checkColumn=(select field1 from table2 where field2='ABC') ) //the select is not scalar

but as I realized it doesn't allow sub-query string in the check condition,I searched a little and read I should use a foreign key,or trigger, or something else,but didn't really understand how to implement those examples here,so posting this as a separate question.

thanks

解决方案

Unfortunately you can not insert Sub Query into context of Check constraint. But here I would like give suggestion, You can use any trigger or function , You can useforeign key constraint to check data dependency I would like to share one example with function.e.g.

CREATE FUNCTION fn_Check_Rollnumber (
    @Rollnumber INT
)
RETURNS VARCHAR(10)
AS
BEGIN
    IF EXISTS (SELECT Rollnumber FROM Table_Student WHERE Rollnumber = @Rollnumber)
        return 'True'
    return 'False'
END

Now you can use this function in you Check context like,

ALTER TABLE Table_Fees 
    WITH CHECK ADD CONSTRAINT CK_RollCheck
    CHECK (fn_Check_Rollnumber(Rollnumber) = 'True')

这篇关于将来自另一个表的数据放入检查约束中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！