Is there a way to enforce the MySQL CHECK constraint for calculated value

Apologies if my wording is very awkward/non-fitting for a programmer – I’m just a SQL noob who’s taking a beginners’ class.

I’m using PHPMyAdmin to design an e-commerce database in which the minimum age of a customer is 18 yo. Because age is a calculated column, I want to record the DOB and enforce a CHECK constraint. I tried this but it didn’t work:

CREATE TABLE Customer(
    ...
    DOB date NOT NULL CHECK(DATEDIFF(year, CURDATE(), DOB))>=18),
    ...);

I got an error saying:

Function or expression ‘curdate()’ cannot be used in the CHECK clause of DOB

Is there a way to enforce the MySQL CHECK constraint for calculated value?

Answer

In MySQL, CHECK constraints apply for all time to a row, not only when data is inserted or updated.

If you read the documentation carefully, you will find this “limitation”:

• 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().

You are trying to add in a constraint using one of these nondeterministic functions. That is the problem.

Note that this is not really a “limitation”. It is the definition of check constraint. Because the values of these functions change, there is no way the database can guarantee that the stored data in the row meets the constraint. You and might know that now() only increases and once the constraint is met then it is always true. It is much harder for the database to know that.

Perhaps the simplest solution is to use a trigger to enforce the constraint when data is inserted into the table.