How can I prevent date overlaps in SQL?

Question

I have the following table structure for a hire table:

hireId int primary key
carId int not null foreign key
onHireDate datetime not null
offHireDate datetime not null

I am attempting to program a multi-user system that does not allow onhire and offhire period for cars to overlap. I need to be able to add hires in a non sequential order. Also need to allow editing of hires.

Any way to constrain the tables or use triggers etc to prevent overlaps? I am using entity framework so I would want to insert to the table as normal and then if it fails throw some catchable exception etc.

the only database-oriented solution I can think of is setting up an `INSTEAD OF` trigger, a procedure that does the check. but I would avoid it and check the overlapping in code! — vulkanino, Mar 06 '12 at 10:09
From an entity framework point of view I don't think I can check easily in the code. I can check at one point but there is the possibility of two people adding/editing events at the same time — GraemeMiller, Mar 06 '12 at 10:17
I thought that entity framework would make data access easier, not harder! It is a simple check after all. The concurrency is an issue, that you could solve with locking, if the architecture allows you to (web/stateless?) — vulkanino, Mar 06 '12 at 10:27
Web site. In EF user gets a context and they can add changes to the context. On saving changes the context writes to the database. If two people add changes to their own context that conflict they would both be persisted. I'm not EF expert however I have seen comments saying the only place to enforce uniqueness on names etc is at the DB level. — GraemeMiller, Mar 06 '12 at 10:32
Instead Of trigger seems to be getting upvoted. Any details? So I would create instead of triggers for insert and update and throw an exception if it detected an overlap? — GraemeMiller, Mar 06 '12 at 10:38

vulkanino · Answer 1 · 2012-03-06T11:07:19.787

3

CREATE TRIGGER tri_check_date_overlap ON your_table
INSTEAD OF INSERT 
AS
BEGIN
    IF @@ROWCOUNT = 0
       RETURN

    -- check for overlaps in table 'INSERTED'
    IF EXISTS(
        SELECT hireId FROM your_table WHERE 
             (INSERTED.onHireDate BETWEEN onHireDate AND offHireDate) OR
             (INSERTED.offHireDate BETWEEN onHireDate AND offHireDate) 
    )
        BEGIN   
           -- exception? or do nothing?
        END
    ELSE
        BEGIN
        END
END
GO

edited Mar 06 '12 at 11:07

answered Mar 06 '12 at 11:01

vulkanino

9,074
7
44
71

It doesn't check for overlaps if two overlapping rows are inserted in a single insert statement (where neither row overlaps an existing row in the table) – Damien_The_Unbeliever Mar 06 '12 at 11:15
Also doesn't this ignore the fact that there are multiple hires to different cars in the same table. So hires can overlap if they are for different cars – GraemeMiller Mar 06 '12 at 14:17

onedaywhen · Accepted Answer · 2012-03-06T13:52:52.057

3

Consider this query:

SELECT *
FROM Hire AS H1, Hire AS H2
WHERE H1.carId = H2.carId
AND H1.hireId < H2.hireId 
AND 
   CASE 
   WHEN H1.onHireDate > H2.onHireDate THEN H1.onHireDate 
   ELSE H2.onHireDate END
   <
   CASE 
   WHEN H1.offHireDate > H2.offHireDate THEN H2.offHireDate 
   ELSE H1.offHireDate END

If all rows meet you business rule then this query will be the empty set (assuming closed-open representation of periods i.e. where the end date is the earliest time granule that is not considered within the period).

Because SQL Server does not support subqueries within CHECK constraints, put the same logic in a trigger (but not an INSTEAD OF trigger, unless you can provide logic to resolve overlaps).

Alternative query using Fowler:

SELECT *
  FROM Hire AS H1, Hire AS H2
 WHERE H1.carId = H2.carId
       AND H1.hireId < H2.hireId 
       AND H1.onHireDate < H2.offHireDate 
       AND H2.onHireDate < H1.offHireDate;

edited Mar 06 '12 at 13:52

answered Mar 06 '12 at 12:05

onedaywhen

55,269
12
100
138

So I shouldn't use Instead Of trigger to throw an exception if overlaps occur? I can catch an exception and get user to fix it. – GraemeMiller Mar 06 '12 at 13:03
@GraemeMiller: Use an `AFTER` trigger. – onedaywhen Mar 06 '12 at 13:06
So I would insert the item and the after trigger would roll it back and raise an error on overlap? – GraemeMiller Mar 06 '12 at 13:27
@GraemeMiller: yes, that's how I would do it. – onedaywhen Mar 06 '12 at 13:46
Just as matter of interest why do you suggest after trigger rather than INSTEAD OF – GraemeMiller Mar 06 '12 at 15:57
2

If you used an `INSTEAD OF` trigger then you would have to write the code to actually `INSERT`/`UPDATE` rows in the base table; with an `AFTER` trigger you don't need to because the updated rows are already in existence. `INSTEAD OF` triggers are used to 'take further action', their primary use being to make am otherwise-unupdatable `VIEW` updatable e.g. updates to a single VIEW can be written to multiple tables within a single `INSTEAD OF` trigger. – onedaywhen Mar 06 '12 at 16:27

score 0 · Answer 3 · edited May 23 '17 at 10:24

I am now using domain driven design techniques. This keeps me from worrying about database triggers etc. and keeps all the logic within the .Net code. Answer is here so that people can see an alternative.

I treat the parent of the collection of periods as an aggregate root and the root has a timestamp. Aggregate root is a consistency boundaries for transactions, distributions and concurrency (see Evans - what I've learned since the blue book). This is used to check the last time any change was confirmed to the database.

I then have methods to add the hire periods to the parent. I test on overlap when adding the movement to the aggregate root. e.g. AddHire(start,end) - will validate that this creates no overlap on the in memory domain object.

AS there is no overlap I save the changes (via my repository), and check the database timestamp is still the same as at the start of the process. Assuming timestamp is the same as it was when I retrieved the entity the changes are persisted and the database updates the timestamp.

If someone else tries to save changes when the aggregate root is being worked on then either I will commit first or they will. If I commit first the timestamps will not match and the overlap check will re-run to make sure that they haven't created an overlap in the intervening time.

I guess the hire collection grows overtime and becomes very big. How do you handle the fact you would have to load an object with a huge collection in memory in order to add a new hire? — Diego Marin Santos, Jan 31 '19 at 17:28
For us it was day level events often going on for weeks. Hundreds of events weren't a problem for us. We did consider specifying a period for overlap detection and only loading events within that period. — GraemeMiller, Mar 16 '19 at 16:42

How can I prevent date overlaps in SQL?

3 Answers3