3

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?

philipxy
  • 14,867
  • 6
  • 39
  • 83
CanadianSQLDummy
  • 31
  • 3
  • 1
    Maybe you can try something like this guy did: https://stackoverflow.com/a/55205025/13868961 – shuberman Jul 05 '20 at 07:11
  • 1
    Thank you so much! It worked!! Despite this being The first time I’ve learned about triggers – CanadianSQLDummy Jul 05 '20 at 16:52
  • I prefer to use explicit code to test for "constraints" rather than depend on "errors" during `INSERTs`. – Rick James Jul 05 '20 at 18:41
  • Does this answer your question? [How to use CURDATE() in check clause?](https://stackoverflow.com/questions/55204218/how-to-use-curdate-in-check-clause) – philipxy Jul 07 '20 at 09:04

1 Answers1

2

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.

Peter O.
  • 32,158
  • 14
  • 82
  • 96
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • 1
    Thank you!! Before today I’ve never heard of or learned about triggers. It was challenging but it worked. – CanadianSQLDummy Jul 05 '20 at 16:52
  • Actually in MariaDB you can add the check constraint and it works, but all the time, when you want to edit the table afterwards, it will throw an error. So for myself I just arranged myself with still using it to ensure, there are no future dates inserted. – Sarah Multitasker May 19 '21 at 10:03