SQL自定义函数实现CHECK约束失效，如何限制仅输入指定工作组？

你的CHECK约束失效是因为NULL值的逻辑判断问题：
当输入非法值时，check_work_group函数里的SELECT work_group FROM...找不到匹配项，会返回NULL。此时testname = NULL的结果是UNKNOWN（不是TRUE也不是FALSE），而SQL Server的CHECK约束只会在表达式结果明确为FALSE时阻止操作，UNKNOWN会被视为允许，所以非法值能顺利插入。

另外还有个小问题：你创建test表时，testname字段只写了nvarchar NOT NULL没指定长度，SQL Server默认会用长度1，这会导致超过1个字符的内容被截断，大概率不是你想要的。

办法1：修复现有函数和约束 #

把函数改成返回布尔值（BIT类型），明确判断输入值是否存在，同时修正字段长度：

1. 先修正test表的字段长度：

ALTER TABLE [dbo].[test] ALTER COLUMN [testname] NVARCHAR(50) NOT NULL;

2. 替换自定义函数：

DROP FUNCTION IF EXISTS [dbo].[check_work_group];
GO
CREATE FUNCTION [dbo].[check_work_group](@testname NVARCHAR(50))
RETURNS BIT
AS
BEGIN
    -- 存在返回1，不存在返回0
    RETURN CASE 
        WHEN EXISTS(SELECT 1 FROM [dbo].[work_groups] WHERE work_group = @testname) 
        THEN 1 
        ELSE 0 
    END;
END;
GO

3. 更新CHECK约束：

ALTER TABLE [dbo].[test] DROP CONSTRAINT IF EXISTS CK_testname;
GO
ALTER TABLE [dbo].[test] ADD CONSTRAINT CK_testname
CHECK (dbo.check_work_group(testname) = 1);
GO

办法2：用外键约束（更推荐） #

外键是SQL专门用来做这种“引用其他表的合法值”的机制，比CHECK+函数更高效，也更符合数据库设计规范：

1. 先确保work_groups表的work_group字段是唯一的（外键必须引用唯一字段）：

-- 如果work_group还不是主键或唯一约束，加一个唯一约束
ALTER TABLE [dbo].[work_groups] ADD CONSTRAINT UQ_work_groups_work_group UNIQUE(work_group);

2. 修正test表的字段长度，再加外键：

ALTER TABLE [dbo].[test] ALTER COLUMN [testname] NVARCHAR(50) NOT NULL;
GO
ALTER TABLE [dbo].[test] ADD CONSTRAINT FK_test_work_group FOREIGN KEY(testname)
REFERENCES [dbo].[work_groups](work_group);
GO

这样以后插入或更新testname时，SQL会自动校验值是否在work_groups里，非法值直接被拦截，不需要额外维护函数。

内容的提问来源于stack exchange，提问作者ConfusedOne