'how to add Check constraints on mysql that age calculate from Date of birth field and validate that age is greater than 18

i have created table using mysql name people which have six attribute id,name,lastname,dob(store in Date data type) ,gender, city and i want that value to be enter in dob(date of birth) to be greater than 18.how do i set this constraint please help

mysqlsql


Solution 1:[1]

MySQL does not support check constraints. So, the only way to do this in MySQL is to create a trigger or wrap the insert in a stored procedure.

The specific logic that you would use is:

dob <= curdate() - interval 18 year

So, in almost any other database, this would look something like:

alter table t add constraint chk_t_dob check (dob <= curdate() - interval '18' year);

(Note: date operations vary among databases.)

Solution 2:[2]

Consider the following, on this the 24th June 2017...

CREATE TABLE dobs(dob DATE NOT NULL);

INSERT INTO dobs (dob)
SELECT '2012-01-01' x FROM (SELECT 1) a WHERE FLOOR(DATEDIFF(NOW(),'2012-01-01') / 365.24) >= 18;

INSERT INTO dobs (dob)
SELECT '1975-01-01' x FROM (SELECT 1) a WHERE FLOOR(DATEDIFF(NOW(),'1975-01-01') / 365.24) >= 18;

INSERT INTO dobs (dob)
SELECT '1999-06-25' x FROM (SELECT 1) a WHERE FLOOR(DATEDIFF(NOW(),'1999-06-25') / 365.24) >= 18;

INSERT INTO dobs (dob)
SELECT '1999-06-24' x FROM (SELECT 1) a WHERE FLOOR(DATEDIFF(NOW(),'1999-06-24') / 365.24) >= 18;

SELECT * FROM dobs;
+------------+
| dob        |
+------------+
| 1975-01-01 |
| 1999-06-24 |
+------------+

Solution 3:[3]

for full explanation consider the link

//use the CHECK constraint to limit the range for age

create table DOB(

age int not null check(age>=18),

);

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Gordon Linoff
Solution 2 Strawberry
Solution 3 Adriaan

Related Questions