How to use CURDATE() in check clause?

Question

I try to create a table where 'dateFrom' and 'dateTo' fields need to be higher than today's date. So I used CHECK like this

CREATE TABLE Booking (
       hotelNo int(10),
       guestNo int(10),
       dateFrom datetime,
       dateTo datetime,
       roomNo int(10),
       CHECK (dateFrom >= CURDATE() AND dateTo >= CURDATE())
);

But I keep getting this error

  ERROR 1901 (HY000): Function or expression 'curdate()' cannot be used in the CHECK clause of `CONSTRAINT_1`

I've searched this on Google many times, but still couldn't figure out a way to do it.

I'm using 10.3.13-MariaDB version. 'CHECK' itself works pretty well but there is a problem with using CURDATE() in CHECK. — gıyaseddin tanrıkulu, Mar 17 '19 at 06:08
Your table data always must be acceptable with CHECK expression. so you can't use an expression that is not permanent over time. you can use triggers to ensure about your inserted data — Muhammad Vakili, Mar 17 '19 at 06:11
Actually this might be the answer that I was looking for, thanks! — gıyaseddin tanrıkulu, Mar 17 '19 at 06:17

score 3 · Accepted Answer · answered Mar 17 '19 at 08:04

3

In MySQL documentation,

Literals, deterministic built-in functions, and operators are permitted. A function is deterministic if, given the same data in tables, multiple invocations produce the same result, independently of the connected user. Examples of functions that are nondeterministic and fail this definition: CONNECTION_ID(), CURRENT_USER(), NOW().

https://dev.mysql.com/doc/refman/8.0/en/create-table-check-constraints.html

So I've used triggers like this instead,

CREATE TRIGGER date_check
BEFORE INSERT ON Booking
FOR EACH ROW
BEGIN
IF NEW.dateFrom <= CURDATE() OR NEW.dateTo <= CURDATE() THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Invalid date!';
END IF;
END

answered Mar 17 '19 at 08:04

gıyaseddin tanrıkulu

73
1
8

And does MariaDB say the same thing? (It seems like `CHECK` is a place where they have diverged.) – Rick James Mar 19 '19 at 19:48
I guess it is. I use MariaDB and `CHECK` works okay. And it has these constraints mentioned in MySQL documentation. – gıyaseddin tanrıkulu Mar 23 '19 at 07:45
@RickJames Yes, I get error `Function or expression 'current_timestamp()' cannot be used in the CHECK clause of CHECK_BIRTHDAY` when trying to check that birthday is in the past with `CHECK(birthday < NOW())`. – izogfif Jun 22 '22 at 15:32

How to use CURDATE() in check clause?

1 Answers1

Linked

Related