7 Replies Latest reply: Aug 5, 2013 10:08 PM by Frank Kulash RSS

    Constraint Help

    a35c130d-ca92-46ca-8177-8a8b5ee02876

      Hello,

       

      Looking for your help. What am I doing wrong here? Basically, I'd like to create a constraint (when creating a table) that checks to ensure that the 'Year' entered is less than or equal to the current year (based off SYSDATE).

       

      Per the code below, I keep getting the same error, "missing right parenthesis". I've spent more than an hour trying different ways to get this to work, but I've been failing miserably.

       

      I am using Oracle 11g Express.

       

      Any help would be greatly appreciated. Thank you.

       

       

      CREATE TABLE TEST

      (Name VARCHAR2(7) PRIMARY KEY,

        Year NUMBER(4),

        CONSTRAINT TEST_YEAR_CK CHECK (Year <= (TO_NUMBER(TO_CHAR(SYSDATE, 'YYYY')))FROM DUAL);

                                                                                                                                                                  *

      ERROR at line 4:

      ORA-00907: missing right parenthesis

        • 1. Re: Constraint Help
          rp0428

          You can't use SYSDATE in a check constraint.


          See this thread from yesterday where Solomon Yakobson shows you how to get the result you want.

          https://forums.oracle.com/thread/2566537

          • 2. Re: Constraint Help
            Frank Kulash

            Hi,

             

            "Missing right parenthesis" occurs for lots of different errors.  In this case, the error has nothing at all to do with parentheses: the error is that you can't call SYSDATE in a CHECK constraint.  Look up "Check Constraints" in the SQL Language manual:

            constraint

            for this and other restrictions.

             

            You could hard-code a year in a check constraint, and then, every year, add a new constraint with a different year, and drop the old one.

             

            Another work-around is to have a BEFORE INSERT OR UPDATE trigger, which raises an error (with a decent error message) if someone tries to enter a year that's too late.

            • 3. Re: Constraint Help
              a35c130d-ca92-46ca-8177-8a8b5ee02876

              Hi Frank,

               

              Thanks for the info. What would be the exact syntax if I wanted to hardcode a date?

               

              I tried the below, but still got that parenthesis error.

               

              CONSTRAINT TEST_YEAR_CK CHECK (Year <= '2013');

              • 4. Re: Constraint Help
                Frank Kulash

                Hi,

                a35c130d-ca92-46ca-8177-8a8b5ee02876 wrote:

                 

                Hi Frank,

                 

                Thanks for the info. What would be the exact syntax if I wanted to hardcode a date?

                 

                I tried the below, but still got that parenthesis error.

                 

                CONSTRAINT TEST_YEAR_CK CHECK (Year <= '2013');

                Almost.  If year is a NUMBER, then you should compare it to another NUMBER, not a VARCHAR2:

                 

                CONSTRAINT TEST_YEAR_2013_CK CHECK (Year <= 2013)

                 

                In other words, lose the single-quotes.

                • 5. Re: Constraint Help
                  a35c130d-ca92-46ca-8177-8a8b5ee02876

                  hmmmm. I tried that as well. Still get the same error (below):

                   

                   

                  SQL> CREATE TABLE TEST

                    2  (Name VARCHAR2(7) PRIMARY KEY,

                    3    Year NUMBER(4),

                    4   CONSTRAINT TEST_YEAR_CK CHECK (Year <= 2013);

                  CONSTRAINT TEST_YEAR_CK CHECK (Year <= 2013)

                                                             *

                  ERROR at line 4:

                  ORA-00907: missing right parenthesis

                  • 6. Re: Constraint Help
                    Frank Kulash

                    Hi,

                     

                    a35c130d-ca92-46ca-8177-8a8b5ee02876 wrote:

                     

                    hmmmm. I tried that as well. Still get the same error (below):

                     

                     

                    SQL> CREATE TABLE TEST

                      2  (Name VARCHAR2(7) PRIMARY KEY,

                      3    Year NUMBER(4),

                      4   CONSTRAINT TEST_YEAR_CK CHECK (Year <= 2013);

                    CONSTRAINT TEST_YEAR_CK CHECK (Year <= 2013)

                                                               *

                    ERROR at line 4:

                    ORA-00907: missing right parenthesis

                    Now you actually are missing a right parenthesis.  The left parenthesis at the beginning of line 2 doesn't have a matching right parenthesis.

                    It helps to indent your code, so that things that have to occur in pairs (like parentheses), if they're separated by a lot of code, occur one right above the other, with only white-space in between them, like this:

                     

                    CREATE TABLE  test

                    ( Name VARCHAR2 (7) PRIMARY KEY,

                       Year NUMBER (4),

                       CONSTRAINT  Test_Year_2013_CK CHECK (Year <= 2013)

                    );

                    • 7. Re: Constraint Help
                      a35c130d-ca92-46ca-8177-8a8b5ee02876

                      LOL - It's been a long day. Ugh!!

                       

                      Thanks for helping me out on this one! Take care.