This content has been marked as final. Show 6 replies
As sb90275 points out, you'd want to compare the date of birth to sysdate (i.e. sysdate >= add_months( date_of_birth, 12 ) ). However, you will not be able to do this in a check constraint-- declarative constraints cannot reference functions like SYSDATE that are not deterministic. Otherwise, a row that was OK yesterday might suddenly no longer be OK today (because the child's 5th birthday came and went) and there is no facility to handle that sort of delayed constraint violation.
You would most likely need to enforce this restriction in the API you implement to insert into the table (I hope you're being taught to use stored procedures for this sort of thing). Barring that, you could also implement the restriction in a trigger. Either way, however, you're going to have the problem of what to do with rows that meet the criteria when they are inserted but then fail to meet the criteria at a later date.
Assuming you are trying to validate the dob based on FUNCTION SYSDATE it may not work as it is a limitation of check constraint.
SQL> create table a (a date, check (round((sysdate-a)/365,1) between 1 and 5));
create table a (a date, check (round((sysdate-a)/365,1) between 20 and 25))
ERROR at line 1:
ORA-02436: date or system variable wrongly specified in CHECK constraint
So you can try and implement a solution like the one given in the URL below.