CODEWITHSUNDEEP
sql-servert-sqlstored-functionscheck-constraints
PrivateJoker
PrivateJoker

Reputation: 791

Check constraint not allowing me to add data

I have a user defined function that returns a BIT.

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

Upvotes: 3

Views: 67

Answers (1)

Nats N
Nats N

Reputation: 41

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

Upvotes: 1

Related Questions