Check constraint that calls function does not work on insert

Question

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

Answer 1

Your primary issues seem:

• The best way to compare intervals is to check the beginning of one to the end of the other, and vice versa. This requires just two AND comparisons.
• You need to exclude the same ID value, so that needs to be passed in.
• Also on SQL Server 2022 you can use the new IS DISTINCT FROM for null comparisons.

IF EXISTS (SELECT 1
    FROM Request IR
    WHERE IR.Item = @Item
      AND IR.Customer = @Customer
      AND IR.Type IS NOT DISTINCT FROM @Type
      AND IR.ID <> @ID
      AND IR.FromDate <= @ToDate    -- do you want <= or <
      AND IR.ToDate >= @FromDate

---

Having said that, using a scalar function like this in a check constraint is often a bad idea from a performance perspective. A trigger might be a better way of enforcing this, as it only runs once per statement, not once per row.

CREATE OR ALTER TRIGGER dbo.QST_CheckRequestEligibility
ON dbo.Request
AFTER INSERT, UPDATE
AS

SET NOCOUNT ON;

IF NOT EXISTS (SELECT 1 FROM inserted)
    RETURN;    -- early bail-out

IF EXISTS (SELECT 1
    FROM inserted i
    JOIN Request IR
       ON IR.Item = i.Item
      AND IR.Customer = i.Customer
      AND IR.Type IS NOT DISTINCT FROM i.Type
      AND IR.ID <> i.ID
      AND IR.FromDate <= i.ToDate    -- do you want <= or <
      AND IR.ToDate >= i.FromDate
)
    THROW 50001, 'You have overlapping dates', 1;

Do not rollback in the trigger. The THROW will handle that.