6 Replies Latest reply: Apr 11, 2011 11:26 AM by nagendra chillale RSS

    Check constraint query

    840290
      Hello, for a Uni assignment we have created a database for a nursery, one of the constraints is each child must be between 1 and 5 years of age.
      I haven't done constraints before and so far have come up with:

      CONSTRAINT CHECK_AGE_MIN CHECK (DOB >= ADD_MONTHS(DOB, 12)),
      CONSTRAINT CHECK_MAX_AGE CHECK (DOB <= ADD_MONTHS(DOB, 60));

      One problem I can see is the second constraint will accept children under 12 months.

      I tried CONSTRAINT CHECK_AGE_RANGE CHECK (DOB BETWEEN ADD_MONTHS(DOB,12) AND ADD_MONTHS(DOB,60));

      This doesn't seem to work either, apologies for my newbness

      Any advice welcome :D
        • 1. Re: Check constraint query
          sb92075
          DOB >= ADD_MONTHS(DOB, 12)),
          how can above EVER be true?
          • 2. Re: Check constraint query
            840290
            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?

            Many thanks
            • 3. Re: Check constraint query
              sb92075
              AGE = SYSDATE - DOB
              • 4. Re: Check constraint query
                JustinCave
                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.

                Justin
                • 5. Re: Check constraint query
                  840290
                  Hi Justin,

                  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!
                  • 6. Re: Check constraint query
                    nagendra chillale
                    Hi,

                    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.

                    http://stackoverflow.com/questions/5332562/using-date-in-a-check-constraint-oracle

                    Regards,
                    NC