using user-defined function for constraint in SQL

Question

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));

GoonerForLife · Accepted Answer · 2023-01-13 03:36:35Z

0

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;

answered Jan 13, 2023 at 3:36

GoonerForLife

7644 silver badges6 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

ConfusedOne Over a year ago

thanks you, that worked! I thought it was an issue with NULL but my solutions to avoid it didn't work :) thank you.

Collectives™ on Stack Overflow

using user-defined function for constraint in SQL

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related