This content has been marked as final. Show 6 replies
DOB >= ADD_MONTHS(DOB, 12)),how can above EVER be true?
lol see what you mean my logic doesn't make any sense, can you throw me a bone as to what I would need to restrict DOB's so only ages of 1 - 5 years are allowed?
AGE = SYSDATE - DOB
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.
We have only been taught SQL fundamentals, nothing on triggers or procedures. Really scratching my head on this one, I guess they would have to physically get birth certificate proof of age and only insert the record if the child was in range!
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.