mysql – INSERT date range into date columns IF dates don’t overlap with existing ones

by

Sadly, using just MySQL this is impossible. Or at least, practically. The preferred way would be using SQL CHECK constraints, these are in the SQL language standard. However, MySQL does not support them.

See: https://dev.mysql.com/doc/refman/5.7/en/create-table.html

The CHECK clause is parsed but ignored by all storage engines.

It seems PostgreSQL does support CHECK constraints on tables, but I’m not sure how viable it is for you to switch database engine or if that’s even worth the trouble just to use that feature.

In MySQL a trigger could be used to solve this problem, which would check for overlapping rows before the insert/update occurs and throw an error using the SIGNAL statement. (See: https://dev.mysql.com/doc/refman/5.7/en/signal.html) However, to use this solution you’d have to use an up-to-date MySQL version.

Apart from pure SQL solutions, this typically is done in application logic, so whichever program is accessing the MySQL database typically checks for these kind of constraints by requesting every row that is violated by the new entry in a SELECT COUNT(id) ... statement. If the returned count is larger than 0 it simply doesn’t to the insert/update.

More Related Contents:

  1. Php date() Date and time is stuck [closed]
  2. Set timezone in PHP and MySQL
  3. How to convert ISO8601 to Date format in php
  4. Date in a URL dd/mm/yyyy
  5. adding 1 day to a DATETIME format value
  6. PHP DateTime supported formats
  7. Display which day it is? sunday or monday “php coding”? [closed]
  8. PHP 01/01/1970 Issues with Date Fields
  9. Convert one date format into another in PHP
  10. Converting string to Date and DateTime
  11. Correctly determine if date string is a valid date in that format
  12. Adding one day to a date
  13. Add ‘x’ number of hours to date
  14. The first day of the current month in php using date_modify as DateTime object
  15. Calculate age based on date of birth
  16. php date validation
  17. Simplest way to increment a date in PHP?
  18. How to store NULL values in datetime fields in MySQL?
  19. How to separate DATE and TIME from DATETIME in MySQL?
  20. PHP order array by date? [duplicate]
  21. How do I find the hour difference between two dates in PHP?
  22. How to display a date as iso 8601 format with PHP
  23. How do you explain the result for a new \DateTime(‘0000-00-00 00:00:00’)?
  24. adding 30 minutes to datetime php/mysql
  25. Formatting an SQL timestamp with PHP
  26. day of the week to day number (Monday = 1, Tuesday = 2)
  27. Subtracting a certain number of hours, days, months or years from date
  28. How do I convert an ISO8601 date to another format in PHP?
  29. Check if laravel model got saved or query got executed
  30. Validate date format in php

Leave a Comment