Check constraint not allowing me to add data

Question

I have a user defined function that returns a BIT.

1 - If dates overlap
0 - If dates do not overlap

I've test the UDF and it appears to be functioning correctly.

This is the following check constraint that I've added:

ALTER TABLE [Gizmo] WITH CHECK ADD CONSTRAINT [CK_DateOverlap]
CHECK [dbo].[MyUserFunction]([GizmoName],[GizmoState],[BeginDate],[EndDate]) = 0;

The table does not have any data in it. When I go to insert a new record I get the error

The INSERT statement conflicted with the CHECK constraint

I manually called the UDF to test and it is returning a 0.

I want the constraint to add the records if the UDF returns a 0. If it returns a 1 I do not want to add it.

What am I missing?

EDIT - UDF

ALTER FUNCTION [dbo].[MyUserFunction]
(
     @GizmoName AS VARCHAR(max),
     @GizmoState AS VARCHAR(max),
     @BeginDate AS DATETIME,
     @EndDate AS DATETIME

)

RETURNS BIT
AS
BEGIN
       DECLARE @Result BIT
       IF EXISTS(
                    SELECT *
                    FROM Gizmos
                    WHERE (@GizmoName = Name AND @GizmoState = [State])
                    AND (@EndDate >= EffectiveFrom AND EffectiveTo >= @BeginDate)
                    )
                    BEGIN
                        SET @Result = 1;
                    END
                    ELSE
                    BEGIN
                           SET @Result = 0;   
                    END
       RETURN @Result
END

INSERT

INSERT INTO Gizmos VALUES('XXX', 'CA', '1/1/2019', '12/31/2019');

I'm not overly familiar with check constraints, but shouldn't the entire statement after the check keyword be inside parens? — Arlo Guthrie, Oct 23 '19 at 13:54
Well the UDF also takes two other parameters, `GizmoName` and `GizmoState`. Is there any chance that these other two inputs could determine the bit output? — Tim Biegeleisen, Oct 23 '19 at 13:55
@ArloGuthrie Maybe, but the OP is calling a UDF for the check constraint, so the parentheses which you _do_ see may in fact belong there. — Tim Biegeleisen, Oct 23 '19 at 13:55
Can we see the UDF ? There could be some issue du to the fact there is actually no datas in the table to check for overlapping — Cid, Oct 23 '19 at 13:56
The UDF and the values you are trying to insert at the very least please. A shortened table definition would be helpful but not critical as long as we know the datatypes. — Sean Lange, Oct 23 '19 at 14:00
@JDS . . . My guess is that the table is not really empty and has one row that conflicts. Or that you have another `check` constraint. Is it possible for you to set up a db<>fiddle? — Gordon Linoff, Oct 23 '19 at 14:30
I believe the problem lies with the order in which the SQL engine runs these statements. The CHECK constraint checks BEFORE INSERT (in other words, it PREVENTS incorrect data from being inserted into the table). At the moment, you're current solution requires the data to first be inserted, and then AFTER INSERT, for the constraint to run (as the constraint is selecting from the same table you're inserting into). You could consider a trigger, or using a computed field on the table and constraining against that — TheDanMan, Oct 23 '19 at 14:35

score 1 · Accepted Answer · answered Oct 24 '19 at 07:17

The row have been inserted into the table and the constraint is applied on the table. So the data already available, the functions returns 1 and the transaction is rolled back. so we need to ignore the first row, then it works as you expected.

CREATE OR ALTER FUNCTION [dbo].[MyUserFunction]
(
     @GizmoName AS VARCHAR(max),
     @GizmoState AS VARCHAR(max),
     @BeginDate AS DATETIME,
     @EndDate AS DATETIME

)

RETURNS BIT
AS
BEGIN
       DECLARE @Result BIT

       IF (SELECT COUNT(Name)
                    FROM Gizmos
                    WHERE (@GizmoName = Name AND @GizmoState = [State])
                    AND (@EndDate >= EffectiveFrom AND EffectiveTo >= @BeginDate)
                    ) > 1
                    BEGIN
                        SET @Result = 1;
                    END
                    ELSE
                    BEGIN
                           SET @Result = 0;   
                    END

                    RETURN @Result

END
GO

Check constraint not allowing me to add data

1 Answers1