check constraints and dates

hi, i'm using informix universal server and i want to create a table like this: create table test( name varchar(50), birthdate date, check (year(TODAY) > (year(birthdate)+18) ) ); When i run this i get a syntax error on the check constraint. What am i doing wrong? Or is there a work-around for this problem? thx in advance, james.

james van hees wrote: > i'm using informix universal server and i want to create a table like this: I hope that means a version later than 9.1x! > create table test( > name varchar(50), > birthdate date, > check (year(TODAY) > (year(birthdate)+18) ) > );> > When i run this i get a syntax error on the check constraint. > What am i doing wrong? Or is there a work-around for this problem? Well, you're attempting to annoy those newly adult 18-year olds who've already had their 18th birthday this year :-) For the rest, there are limitations on using TODAY and CURRENT in the check constraints - I'm not sure it is possible at all, even. The trouble is the "moving arrow of time". The CHECK condition is supposed to be valid for all time, yet it is hard for the database to ensure that when one term of the condition is continually changing. OK, in this case a more intelligent optimizer might be able to determine that if the condition is satisfied on date D, it will also be satisfied on all dates after D. However, let's change the criterion - only minors are allowed in the table: CREATE TABLE minors ( name VARCHAR(50) NOT NULL, birthdate DATE NOT NULL, CHECK (birthdate > TODAY - 18 * 365 + 4) ); There are at least 4 and sometimes 5 leap years in a period of 18 years - hence the constants in the calculation. Now, if I insert a value and it validates OK now, it may not validate as OK tomorrow. The database does not allow this - and takes the overly conservative view that all calculations involving TODAY or CURRENT are not a good idea. -- Jonathan Leffler #include <disclaimer.h> Email: jleffler@earthlink.net, jleffler@us.ibm.com Guardian of DBD::Informix v2003.04 -- http://dbi.perl.org/

"Jonathan Leffler" <jleffler@earthlink.net> wrote in message news:3EB7DF2E.4030505@earthlink.net... > james van hees wrote: > > i'm using informix universal server and i want to create a table like this: > > I hope that means a version later than 9.1x! > > > create table test( > > name varchar(50), > > birthdate date, > > check (year(TODAY) > (year(birthdate)+18) ) > > );> > > > When i run this i get a syntax error on the check constraint. > > What am i doing wrong? Or is there a work-around for this problem? > > > Well, you're attempting to annoy those newly adult 18-year olds who've > already had their 18th birthday this year :-) > > For the rest, there are limitations on using TODAY and CURRENT in the > check constraints - I'm not sure it is possible at all, even. The > trouble is the "moving arrow of time". The CHECK condition is > supposed to be valid for all time, yet it is hard for the database to > ensure that when one term of the condition is continually changing. > OK, in this case a more intelligent optimizer might be able to > determine that if the condition is satisfied on date D, it will also > be satisfied on all dates after D. However, let's change the > criterion - only minors are allowed in the table: > > CREATE TABLE minors > ( > name VARCHAR(50) NOT NULL, > birthdate DATE NOT NULL, > CHECK (birthdate > TODAY - 18 * 365 + 4) > );> > There are at least 4 and sometimes 5 leap years in a period of 18 > years - hence the constants in the calculation. Now, if I insert a > value and it validates OK now, it may not validate as OK tomorrow. > The database does not allow this - and takes the overly conservative > view that all calculations involving TODAY or CURRENT are not a good idea. > > thx for clarifying that out for me. But is there no way to set a constraint such that only people above 18 years are allowed in the database (if birthday is stored in db)? regards, james

Hi, You can call a stored procedure(you can check here the age) within an insert trigger on your table. -- Ciao. Marino Decaro

You could have another column relating to the insertion date of the row. The check constraint would verify that the row was inserted at least 18 years after the birthday. Cheers Serge -- Serge Rielau DB2 UDB SQL Compiler Development IBM Software Lab, Toronto Visit DB2 Developer Domain at http://www7b.software.ibm.com/dmdd/