I created a constraint that prevents adding new records if it finds an inserted record has the same item, customer, type, and also, start and end dates overlapping.

I get the following error when I insert a test record into the table. Even when the table is empty I get this error:
The INSERT statement conflicted with the CHECK constraint "CK_Inventory_DateRange". The conflict occurred in database "xxx", table "dbo.Request".

More details about the table and constraint are below.

Request table

CREATE TABLE Request
(
  ID INT IDENTITY(1,1) PRIMARY KEY,
  Customer VARCHAR(15) NOT NULL,
  Type VARCHAR(5) NULL, 
  Item VARCHAR(31) NOT NULL,
  FromDate DATE NOT NULL,
  ToDate DATE NOT NULL,
  Quantity INT NOT NULL,
  CONSTRAINT CK_Inventory_DateRange CHECK (dbo.fnQST_CheckRequestEligibility(Item, Customer, Type, FromDate, ToDate) = 1)
);

Scalar Valued function

ALTER FUNCTION dbo.fnQST_CheckRequestEligibility
(
    @Item VARCHAR(31),
    @Customer VARCHAR(15),
    @Type VARCHAR(5) = NULL,
    @FromDate DATE,
    @ToDate DATE
)
RETURNS INT
AS
BEGIN
    DECLARE @Result INT = 1;

    IF EXISTS 
    (
    SELECT 1 FROM Request IR
    WHERE IR.Item = @Item
    AND IR.Customer = @Customer
    AND ISNULL(IR.Type, 'NULL') = ISNULL(@Type, 'NULL')
    AND (
        (IR.FromDate <= @FromDate AND IR.ToDate >= @FromDate)
        OR (IR.FromDate <= @ToDate AND IR.ToDate >= @ToDate)
        OR (@FromDate <= IR.FromDate AND @ToDate >= IR.FromDate)
        OR (@FromDate <= IR.ToDate AND @ToDate >= IR.ToDate)
        )
     )
     BEGIN
    SET @Result = 0;
     END    

    RETURN @Result;
END;
GO

Test insert statement

INSERT INTO [dbo].[Request]
           ([Customer]
           ,[Type]
           ,[Item]
           ,[FromDate]
           ,[ToDate]
           ,[Quantity])
     VALUES
           ('TestCustomer'
           ,NULL
           ,'TestItem'
           ,'2023-03-23'
           ,'2023-05-23'
           ,100)

Expected result

• INSERT INTO [dbo].[Request]([Customer],[Type],[Item],[FromDate],[ToDate],[Quantity]) VALUES ('TestCustomer',NULL,'TestItem','2023-03-23','2023-05-23',100) inserts into the table when the table is empty and if there is a record with same item, customer, type, and overlapping dates insert statement should fail to insert that record into the table.

What I tried

• calling fnQST_CheckRequestEligibility alone with same parameters which returns 1 so it should be inserting