select in check constraint

Question

I want to create a check constraint for my table, where you can't add new line, if the new booking range(start_date, end_date) have intersect with an already submitted row. But i can't place query in a check constraint. Do you have an idea how to do this?

The tables:

APARTMAN

        id INT
        price INT

BOOKINGS

        id INT
        start_date DATE
        end_date DATE
        apartman_id INT

[apartman_id] IN (SELECT [id]  FROM [dbo].[APARTMAN]
    WHERE [id]  NOT IN (
        SELECT [apartman_id]    FROM
            [dbo].[BOOKINGS]
        WHERE
            ([start_date] <= "requested end_date" AND
            [end_date] >= "requested start_date" )
            OR
            ([start_date] <= "requested start_date" AND
             [end_date] >= "requested end_date" )
            OR
            (([start_date] <= "requested end_date" AND  [end_date] >= "requested start_date" )
                OR
                ([end_date] <= "requested start_date"  AND  [end_date] >= "requested end_date" ))
    )
)

To look at it from another direction, would it be acceptable to have the insertion done through a stored proc? — Hotchips, Mar 26 '13 at 23:24
I would recommend a trigger rather than a table-level constraint. Do you have to guard updates as well? — Aaron Bertrand, Mar 26 '13 at 23:35
@Hotchips you can't really use a stored procedure to protect the data, because people can access the table without going through the stored procedure. Sure, people can disable triggers too, but that is intentional malice (and if that is the worst thing someone with that much privilege does to harm your database, consider yourself quite lucky). You can use stored procedures *also* but they shouldn't be the only line of defense. — Aaron Bertrand, Mar 26 '13 at 23:37

score 2 · Accepted Answer · edited May 23 '17 at 10:32

Here's an instead of trigger that I think handles all scenarios.

CREATE TRIGGER dbo.PreventOverlappingBookings
ON dbo.BOOKINGS INSTEAD OF INSERT, UPDATE
AS
BEGIN
  SET NOCOUNT ON;

  IF EXISTS (
    SELECT 1 FROM inserted AS i
      INNER JOIN dbo.BOOKINGS AS b
      ON (b.id <> i.id OR i.id = 0) -- 0 for insert
      AND b.apartman_id = i.apartman_id
      AND ((b.start_date   <= i.end_date   AND b.end_date >= i.start_date)
         OR (b.start_date  <= i.start_date AND b.end_date >= i.end_date)
         OR (b.end_date    <= i.start_date AND b.end_date >= i.end_date))
  ) OR EXISTS (
    -- also make sure there are no overlaps in a set-based insert/update
    SELECT 1 FROM inserted AS i
      INNER JOIN inserted AS b
      ON (b.id <> i.id OR i.id = 0) -- 0 for insert
      AND b.apartman_id = i.apartman_id
      AND ((b.start_date   <= i.end_date   AND b.end_date >= i.start_date)
         OR (b.start_date  <= i.start_date AND b.end_date >= i.end_date)
         OR (b.end_date    <= i.start_date AND b.end_date >= i.end_date))
  )
  BEGIN
    RAISERROR('Overlapping date range.', 11, 1);
  END
  ELSE
  BEGIN
      UPDATE b SET start_date = i.start_date, end_date = i.end_date
        FROM dbo.BOOKINGS AS b
        INNER JOIN inserted AS i
        ON b.id = i.id;
      IF @@ROWCOUNT > 0
      BEGIN
        INSERT dbo.BOOKINGS(start_date, end_date, apartman_id)
          SELECT start_date, end_date, apartman_id FROM inserted AS i;
      END
  END
END
GO

Some answers will suggest a function in a UDF, but I don't trust them (and neither should you, IMHO).

select in check constraint

1 Answers1