CODEWITHSUNDEEP
mysqlsql
Fragan
Fragan

Reputation: 892

MySQL CHECK age from birthday

I have a MySQL table with a birthdate attribute, :

CREATE TABLE IF NOT EXISTS `MyTable` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) NOT NULL,
  `birthdate` date NOT NULL,
   PRIMARY KEY (`ID`)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

I want to check for every inserted row that the age is < 20.

I tried to do this with this check :

CONSTRAINT CHK_Age CHECK (DATEDIFF(date_naissance,CURDATE())/365.25 >=20)

But sadly, it's not working and im getting this error function or expression CURDATE() cannot be used in check

Anyone has an idea on how i can make this work ?

Upvotes: 0

Views: 178

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269803

You cannot do what you want with a check constraint. The limitation is based on your needing to use a non-deterministic function. The exact language in the documentation is:

  • 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 can do this using a trigger, instead. Or, you could set up a daily job to delete records that don't match.

The above is a little abstract. The basic rule is that a check constraint is not simply validating the data when it is inserted; it is stating a condition that is always true on the table. The age clearly changes over time, so (in theory) the constraint could be violated without any changes to the table.

Upvotes: 2

Mohammed Al-Najjar
Mohammed Al-Najjar

Reputation: 24

You can do this using YEAR, CURRENT_TIMESTAMP

SELECT YEAR(CURRENT_TIMESTAMP) - YEAR(birthdate) as age FROM MyTable where age < 20

Upvotes: 0

Related Questions