1

SQL newbie here. I currently have a 2-table database-"Customers" and "Bookings". This question concerns the "Booking" table, which has 3 attributes this question is about:

  • BookDate
  • BookStart
  • BookEnd

    I'm trying to prevent the timeslots from overlapping. To do so, I've managed to bind the 3 attributes together into a composite key, which prevents identical entries from being entered. However, if 2 records like so are entered:

    24/05/18 10:00am 10:15am
    
    24/05/18 10:00am 10:30am
    

The records are accepted into the system. How do I prevent this? I'm trying to use data validation field right now (Help with preventing double bookings with variable lengths), but I'm not quite sure how to link 2 attributes together within it.

Thanks in advance!

  • It seems You use MS Access (question is tagged by [ms-access]). 3 Datetime fields needed 3*8=24 bytes to store. You convert it to string data type with minimal length equal to 16 bytes needed at least 10+16=26 bytes to store. Plus the problem under consideration. Where is Your profit? – Akina May 17 '18 at 9:22
0

You can only achieve such validation at the database level (when not using forms and VBA) using a CHECK constraint

ALTER TABLE Bookings 
ADD CONSTRAINT NoDoubleBookings CHECK(
       NOT EXISTS(
             SELECT 1 
             FROM Bookings b1, Bookings b2 
             WHERE b1.BookDate + b1.BookStart < b2.BookDate + b2.BookStart 
             AND b1.BookDate + b1.BookEnd > b2.BookDate + b2.BookStart
       )
)

More details on adding check constraints to Access databases can be found here

  • Have you tested / tried this in Access? Does it allow this type of constraints (with subqueries)? – ypercubeᵀᴹ May 17 '18 at 9:35
  • @ypercube Access certainly allows these. I haven't tested this specific constraint, but I've used CHECK constraints in Access. You can follow the link to find more information about adding CHECK constraints, since you can't execute them through the normal GUI unless you've set Access to ANSI 92 compatible mode, and you can't execute them using DAO, but you can execute them using ADO. – Erik A May 17 '18 at 9:37
  • I'm not asking about CHECK constraint, I'm asking about CHECK constraints with subqueries. – ypercubeᵀᴹ May 17 '18 at 10:13
  • @ypercubeᵀᴹ Yes, those are allowed (I wouldn't know why you should use one without a subquery, you can use a normal constraint for that, so I assumed I didn't have to specify that). To ease your mind, I validated this specific rule too now. – Erik A May 17 '18 at 10:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.