0

I'm trying to add a constraint to a SQL Server table with the following script:

CREATE FUNCTION CheckHREmpIDRequired()
RETURNS BIT AS
BEGIN
    DECLARE @ret BIT;
    SET @ret = 0;
    SELECT @ret = 1 WHERE EXISTS (SELECT 1 FROM GeneralSettings WHERE [Key] = 'HR_EMP_ID_REQUIRED' AND TRIM(LOWER([Value])) = 'true');
    RETURN @ret;
END
GO

CREATE FUNCTION CheckHREmpIDAndDomainValid(@HREmpID varchar(10), @Domain varchar(50))
RETURNS BIT AS
BEGIN
  DECLARE @required BIT;
  EXEC @required = dbo.CheckHREmpIDRequired;
  IF ((@HREmpID IS NULL OR TRIM(@HREmpID) = '') AND @required = 1)
  BEGIN
    RETURN 0
  END
  IF (@HREmpID IS NOT NULL AND LEN(@HREmpID) > 0)
  BEGIN
    DECLARE @ret BIT;
    SET @ret = 0;
    SELECT @ret = 1 WHERE NOT EXISTS (SELECT 1 FROM Employee WHERE HREmpID = @HREmpID AND Domain = @Domain);
    RETURN @ret;
  END
  RETURN 1
END
GO

ALTER TABLE [dbo].[Employee]
    ADD CONSTRAINT [CheckUniqueHREmpIDAndDomain] CHECK (dbo.CheckHREmpIDAndDomainValid(dbo.Employee.HREmpID, dbo.Employee.Domain) = 1);
GO

The constraint basically checks that the columns HREmpID and Domain, in combination, are unique. In the case where the HREmpID is not required and is not supplied, duplicates are allowed.

There are two functions that are called in the constraint: CheckHREmpIDAndDomainValid and CheckHREmpIDRequired. CheckHREmpIDAndDomainValid is the main function that's called from the constraint. It checks that the HREmpID and Domain combination is unique and that, if required, the HREmpID is supplied. CheckHREmpIDRequired checks whether HREmpID is required or not.

However, when I run this script, it tells me:

The ALTER TABLE statement conflicted with the CHECK constraint "CheckUniqueHREmpIDAndDomain". The conflict occurred in database "TBoardDB", table "dbo.Employee".

But all records in the Employee table are unique:

enter image description here

And the HREmpID is not required.

What is conflicting with the constraint? Does anyone see a problem with the script?

gib65
  • 1,709
  • 3
  • 24
  • 58
  • Hmm, the second function returns `0` if a corresponding row exists but if it exists the function is called and therefore the constraint is violated. To test that you could (temporarily) empty the table and see if the error then goes away. – sticky bit Mar 18 '21 at 13:42
  • `NOT EXISTS` will always return false as you have the row you are working on, you need `COUNT(*)... > 1`. But you may find https://stackoverflow.com/a/65827550/14868997 interesting, it's a much better solution to a multi-table constraint – Charlieface Mar 18 '21 at 13:43
  • When did a udf in a check constraint become better than a trigger? Reminds me of when a developer used scalar udfs everywhere for code refactoring. Performance was horrible. I am very skeptical of this approach. The scalar udf can also hide performance metrics such as logical io and cpu. – Randy in Marin Mar 18 '21 at 19:17
  • To be honest, you don't actually need any of this. Your unique constraint references only one table, so model that as a unique `HREmpID,Domain`. Then instead of `CheckHREmpIDRequired` just disable the constraint – Charlieface Mar 19 '21 at 01:22

0 Answers0