This discussion is archived
7 Replies Latest reply: Aug 4, 2013 3:02 PM by rp0428 RSS

Create constraint to check age based on DOB

8bb7968b-e6ae-456c-8459-f418352e9e0a Newbie
Currently Being Moderated

Hello,

 

In creating my database (using Oracle 11g Express), specifically my employee table, is there a way to create a constraint to ensure that the employee is equal to, or older than, 18 years old?

 

I do not want to ask people their age. I'd like to do it based off their DOB (as below). E.G. - The 'DOB' value listed in the below table.

 

Thanks for your help!

 

CREATE TABLE EMPLOYEE

(EmpNo NUMBER(6) PRIMARY KEY,

DOB DATE,

HireDate DATE DEFAULT GETDATE());

  • 1. Re: Create constraint to check age based on DOB
    8bb7968b-e6ae-456c-8459-f418352e9e0a Newbie
    Currently Being Moderated

    I'm thinking this may be a constraint that cannot be applied until after the table is already created? Looking for your input/suggestions.

     

    Thanks!

  • 2. Re: Create constraint to check age based on DOB
    Solomon Yakobson Guru
    Currently Being Moderated

    It can't be done via check constraint. Use before insert for each row trigger if you want database to validate employee age when adding an employee:

     

     

    CREATE OR REPLACE

      TRIGGER EMPLOYEE_BIR

        BEFORE INSERT

        ON EMPLOYEE

        FOR EACH ROW

        BEGIN

            IF ADD_MONTHS(DOB,216) > TRUNC(SYSDATE)

              THEN

                 RAISE_APPLICATION_ERROR(-20950,'Employee is under 18!');

            END IF;

    END;

    /

     

    And Oracle != SQL Server. It is SYSDATE, not GETDATE().

     

    SY.

  • 3. Re: Create constraint to check age based on DOB
    8bb7968b-e6ae-456c-8459-f418352e9e0a Newbie
    Currently Being Moderated

    Hi SY,

     

    Thanks for the info AND the correction on the SYSDATE statement

     

    A couple quick questions, shouldn't your logic be "<" instead of ">"?

     

    For the TRUNC function, do you need to specify the unit of measure? Likewise, do we need to use the ADD_MONTHS function against the SYSDATE? That way we are comparing apples to apples? Maybe I'm just not fully understanding the logic.

     

    Again, thanks for your help!

  • 4. Re: Create constraint to check age based on DOB
    Solomon Yakobson Guru
    Currently Being Moderated

    TRUNC date defaults to beginning of day, so TRUNC(SYSDATE) is today 00:00:00. And 216 months is 18 x 12. So DOB + 216 months is date when person turns 18. And if that is > TRUNC(SYSDATE) then person is not yet 18 years old.

     

    SY.

  • 5. Re: Create constraint to check age based on DOB
    8bb7968b-e6ae-456c-8459-f418352e9e0a Newbie
    Currently Being Moderated

    Ah, thank you for explaining. Very much appreciated!

  • 6. Re: Create constraint to check age based on DOB
    rp0428 Guru
    Currently Being Moderated

    In creating my database (using Oracle 11g Express), specifically my employee table, is there a way to create a constraint to ensure that the employee is equal to, or older than, 18 years old?

     

    Sure - There are two ways to use  a CHECK constraint to do that.

     

    One way is to use a CHECK constraint that is based on a DETERMINISTIC function that you write. The function has to be DETERMINISTIC to be allowed.

     

    Another way (11g or later) is to use a VIRTUAL column and put the check constraint on that column. See this article for an example.

    http://rwijk.blogspot.com/2007/12/check-constraints-with-sysdate.html

     

    There are two other issues you may also need to deal with.

     

    1. What about updates to the table? You need to make sure the value cannot be updated to a date that would violate your business rule

     

    2. What about timezone issues? Someone that is 18 years old in one timezone may not be 18 years old in a different timezone. DATE datatypes don't include timezone and depending on where the source data is coming from the client timezone may be different than the server timezone.

     

    Make sure you fully document the EXACT business rules that you implement. IMHO if you want to claim that you implemented an 18 or over rule you either need to take timezone into account or affirmatively state that you do NOT cover cases where timezone might play a role.

  • 7. Re: Create constraint to check age based on DOB
    Solomon Yakobson Guru
    Currently Being Moderated

    rp0428 wrote:


    Sure - There are two ways to use  a CHECK constraint to do that.

    One way is to use a CHECK constraint that is based on a DETERMINISTIC function that you write. The function has to be DETERMINISTIC to be allowed.

    Don't confuse check constraint & FBI. Deterministic or not, you can't use user-defined functions in check constraint (and, BTW, it is also shown in article you reference):

     

    SQL> create or replace
      2    function today
      3      return date
      4      deterministic
      5      is
      6      begin
      7          return trunc(sysdate);
      8  end;
      9  /

    Function created.

    SQL> create table tbl(dob date)
      2  /

    Table created.

    SQL> alter table tbl
      2    add constraint tbl_chk1
      3      check(
      4            add_months(dob,216) <= today
      5           )
      6  /
              add_months(dob,216) <= today
                                     *
    ERROR at line 4:
    ORA-00904: "TODAY": invalid identifier


    SQL>

     

    rp0428 wrote:


    Sure - There are two ways to use  a CHECK constraint to do that.

    Another way (11g or later) is to use a VIRTUAL column and put the check constraint on that column.

     

    One should realize what they are getting into when declaring non-deterministic function as deterministic. Let's take Rob van Wijk's article you reference. One thing Rob didn't mention - virtual column doesn't store insert/update calculated value. It is calculated each time you select. This is whole idea behind Rob's example. That's why his function and column say mydate_in_past. Rob's example tells you if column mydate value is in the past NOW and that NOW is changing (I didn't add check constraint yet):

     

    SQL> insert
      2    into mytable(id,mydate)
      3    values(
      4           1,
      5           sysdate + interval '10' second
      6          )
      7  /

    1 row created.

    SQL> select  *
      2    from mytable
      3  /

            ID MYDATE    M
    ---------- --------- -
             1 04-AUG-13 N

    SQL> /

            ID MYDATE    M
    ---------- --------- -
             1 04-AUG-13 N

    SQL> /

            ID MYDATE    M
    ---------- --------- -
             1 04-AUG-13 N

    SQL> /

            ID MYDATE    M
    ---------- --------- -
             1 04-AUG-13 Y

    SQL>

     

    As you can see Rob's solution will work for check constraint checking if at insert/update time value is in the past (mydate_in_past_ind = 'Y'). It will not work if we want check for future dates (mydate_in_past_ind = 'N'). Unfortunately we are not growing younger, so yes, Rob's solution will work for check constraint "employee is 18 or older", but at a cost of creating an inconvenience in coding. Now I can't use asterisk in insert. I can't use %ROWTYPE in insert values clause. Each time I use asterisk in select, virtual column is calculated and therefore function behind it is executed, etc. So in this particular case I'd use trigger.

     

    SY.

Incoming Links

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points