using user-defined function for constraint in SQL-CodePudding

I am trying to add a check constraint to my table in SQL database so that user can only enter the work_group in the column that is in the 'approved list' - dbo.work_groups.

I've read a few forums and created the following table, user defined function and constraint.

dbo.work_groups is populated with the list of work groups, i.e. 'Admin','Accountant', 'Engineer'.

When I enter the above work groups into dbo.test it accepts them. But it also accepts any other values. How can I limit the constraint only to those work groups that are listed in the dbo.work_groups. What have I done wrong? Thanks.

-----test table

CREATE TABLE [dbo].[test] ([testname] nvarchar NOT NULL)

-----user defined function

CREATE FUNCTION [dbo].[check_work_group](@testname NVARCHAR(50))

RETURNS NVARCHAR(50) AS

BEGIN

RETURN (SELECT work_group FROM [dbo].[work_groups] WHERE work_group=@testname) END;

-----constraint

ALTER TABLE test ADD constraint CK_testname CHECK (testname=dbo.check_work_group(testname));

CodePudding user response：

I think the issue with the function. When there is no record in the work_groups table for a given parameter value, it is returning null, which is causing the issue. One way to solve it is by forcing the function to return a value that does not exist in work_groups table ('-1' in the example below) when no record is found for the given parameter value.

CREATE FUNCTION [dbo].[check_work_group](@testname NVARCHAR(50))
RETURNS NVARCHAR(50) AS
BEGIN
    RETURN COALESCE((SELECT work_group FROM [dbo].[work_groups] WHERE work_group=@testname), '-1'); 
END;