12 Replies Latest reply: Apr 30, 2013 9:24 AM by Smaipady RSS

    Missing right parent while creating table.

    1006335
      I get the "ORA-00907: missing right parenthesis" error while creating the table with the code below.

      create table SALES_ORDER
      (
      ORDERNO varchar2(6) Primary key check(ORDERNO like 'O%'),
      CLIENTNO varchar2(6) foreign key references CLIENT_MASTER(CLIENTNO),
      ORDERDATE date not null,
      DELYADDR varchar2(25),
      SALESMANNO varchar2(6) foreign references SALESMAN_MASTER(SALESMANNO),
      DELYTYPE char(1) default 'F' check(DELYTYPE IN('P','F')),
      BILLYN char(1),
      DELYDATE date check(DELYDATE >= ORDERDATE),
      ORDERSTATUS varchar2(10) check(ORDERSTATUS IN('In Process','Fulfilled','BackOrder','Cancelled'))
      );

      What am i doing wrong?
        • 1. Re: Missing right parent while creating table.
          Paul  Horth
          1003332 wrote:
          I get the "ORA-00907: missing right parenthesis" error while creating the table with the code below.

          create table SALES_ORDER
          (
          ORDERNO varchar2(6) Primary key check(ORDERNO like 'O%'),
          CLIENTNO varchar2(6) foreign key references CLIENT_MASTER(CLIENTNO),
          ORDERDATE date not null,
          DELYADDR varchar2(25),
          SALESMANNO varchar2(6) foreign references SALESMAN_MASTER(SALESMANNO),
          DELYTYPE char(1) default 'F' check(DELYTYPE IN('P','F')),
          BILLYN char(1),
          DELYDATE date check(DELYDATE >= ORDERDATE),
          ORDERSTATUS varchar2(10) check(ORDERSTATUS IN('In Process','Fulfilled','BackOrder','Cancelled'))
          );

          What am i doing wrong?
          Try
          create table SALES_ORDER
          (
          ORDERNO varchar2(6) Primary key check(ORDERNO like 'O%'),
          CLIENTNO varchar2(6) ,
          ORDERDATE date not null,
          DELYADDR varchar2(25),
          SALESMANNO varchar2(6) ,
          DELYTYPE char(1) default 'F' check(DELYTYPE IN('P','F')),
          BILLYN char(1),
          DELYDATE date,
          ORDERSTATUS varchar2(10) check(ORDERSTATUS IN('In Process','Fulfilled','BackOrder','Cancelled')),
          foreign key (clientno) references CLIENT_MASTER(CLIENTNO),
          foreign key (salesmanno) references SALESMAN_MASTER(SALESMANNO)
          );
          I have removed the check on delydate as you cannot refer to a different column in a check constraint (at the column level). you can at the table level.

          Edited by: Paul Horth on Apr 30, 2013 2:57 PM
          Updated to clarify where cross-column checking can happen (need more coffee!)
          • 2. Re: Missing right parent while creating table.
            Solomon Yakobson
            Get rid of foreign key and foreign. All you need is references:
            create table SALES_ORDER
            (
            ORDERNO varchar2(6) Primary key check(ORDERNO like 'O%'),
            CLIENTNO varchar2(6) references CLIENT_MASTER(CLIENTNO),
            ORDERDATE date not null,
            DELYADDR varchar2(25),
            SALESMANNO varchar2(6) references SALESMAN_MASTER(SALESMANNO),
            DELYTYPE char(1) default 'F' check(DELYTYPE IN('P','F')),
            BILLYN char(1),
            DELYDATE date check(DELYDATE >= ORDERDATE),
            ORDERSTATUS varchar2(10) check(ORDERSTATUS IN('In Process','Fulfilled','BackOrder','Cancelled'))
            );
            SY.
            • 3. Re: Missing right parent while creating table.
              Frank Kulash
              Hi,

              Welcome to the forum!
              1003332 wrote:
              I get the "ORA-00907: missing right parenthesis" error while creating the table with the code below.
              That error message occurs with a large variety of syntax errors. Misplaced parentheses might be the single greatest cause of the error, but the problem is usually something else.

              In this case, the problem is in the syntax of the FOREIGN KEY and multi-column CHECK constraints.
              create table SALES_ORDER
              (
              ORDERNO varchar2(6) Primary key check(ORDERNO like 'O%'),
              CLIENTNO varchar2(6) foreign key references CLIENT_MASTER(CLIENTNO),
              ORDERDATE date not null,
              DELYADDR varchar2(25),
              SALESMANNO varchar2(6) foreign references SALESMAN_MASTER(SALESMANNO),
              DELYTYPE char(1) default 'F' check(DELYTYPE IN('P','F')),
              BILLYN char(1),
              DELYDATE date check(DELYDATE >= ORDERDATE),
              ORDERSTATUS varchar2(10) check(ORDERSTATUS IN('In Process','Fulfilled','BackOrder','Cancelled'))
              );

              What am i doing wrong?
              Review the correct syntax in the SQL Language manual.
              • 4. Re: Missing right parent while creating table.
                1006335
                The solution in the first reply worked.
                The problem was i think with both the 'reference statement' as well as the 'check condition'.
                As pointed out in the solution the 'check(DELYDATE >= ORDERDATE)' constraint was referencing another column.
                So i removed the check and it worked.

                But i still don't know how to implement the "DELYDATE >= ORDERDATE" condition!
                Would be helpful if someone could tell how?

                Edited by: 1003332 on Apr 30, 2013 6:30 AM
                • 5. Re: Missing right parent while creating table.
                  Paul  Horth
                  1003332 wrote:
                  The solution above worked.
                  The problem was i think with both the 'reference statement' as well as the 'check condition'.
                  As pointed out above the check condition was referencing another column.

                  But i still don't know how to implement the "DELYDATE >= ORDERDATE" condition!
                  Would be helpful if you could tell how?
                  There are a number of approaches you could take.

                  1. Do the checking just in the front-end/middle-tier.
                  2. Do it in the front-end/middle-tier and by having a PL/SQL API that handles the application logic from a DB point-of-view.
                  3. Do it in the front-end/middle-tier and by using normal SQL (i.e. inserts etc.) and having a trigger on the table that checks for cross-column constraints.

                  Simplifying things a bit, the choice depends on a number of factors - for example, whether the 'rule' is application specific or something that applies to the
                  whole business/real world. In the case above I would think it is 'real world' so would not choose option 1.
                  Whether you use a PL/SQL API or triggers also depends. I know some don't like to use triggers but for cross-column checking, I don't think they are too bad.
                  Personally, however, I would tend to push for Option 2: PL/SQL API procedures that are called from the middle-tier to perform the DB logic and any necessary checking.
                  • 6. Re: Missing right parent while creating table.
                    bencol
                    Using a virtual column? http://docs.oracle.com/cd/E11882_01/server.112/e25494/tables006.htm#ADMIN12494
                    11g> CREATE TABLE test
                      2    (d1 date
                      3    ,d2 date
                      4    );
                    
                    Table created.
                    
                    11g>
                    11g> alter table test add date_diff as (d1-d2);
                    
                    Table altered.
                    
                    11g>
                    11g> alter table test add constraint text_ck01 check (date_diff >0);
                    
                    Table altered.
                    
                    11g> insert into test(d1,d2) values (sysdate,sysdate-1);
                    
                    1 row created.
                    
                    11g> insert into test(d1,d2) values (sysdate,sysdate+1);
                    insert into test(d1,d2) values (sysdate,sysdate+1)
                    *
                    ERROR at line 1:
                    ORA-02290: check constraint (TEXT_CK01) violated
                    • 7. Re: Missing right parent while creating table.
                      John Spencer
                      1003332 wrote:
                      The solution in the first reply worked.
                      The problem was i think with both the 'reference statement' as well as the 'check condition'.
                      As pointed out in the solution the 'check(DELYDATE >= ORDERDATE)' constraint was referencing another column.
                      So i removed the check and it worked.

                      But i still don't know how to implement the "DELYDATE >= ORDERDATE" condition!
                      Would be helpful if someone could tell how?

                      Edited by: 1003332 on Apr 30, 2013 6:30 AM
                      You cannot create an in-line check constraint referencing multiple columns, but yiu can add the constraint after creating the table. I used your create table statement, removing the FK constraints so I didn't have to create multiple tables, then did:
                      SQL> alter table SALES_ORDER add constraint
                        2  dely_to_order check(DELYDATE >= ORDERDATE);
                      Table altered.
                      
                      SQL> insert into sales_order values ('O1', '123', sysdate -1, 'address', 'sales1',
                        2                                  'F', 'Y', sysdate, 'In Process');
                      
                      1 row created.
                      
                      SQL> insert into sales_order values ('O2', '123', sysdate, 'address', 'sales1',
                        2                                  'F', 'Y', sysdate - 1, 'In Process');
                      insert into sales_order values ('O2', '123', sysdate, 'address', 'sales1',
                      *
                      ERROR at line 1:
                      ORA-02290: check constraint (OPS$ORACLE.DELY_TO_ORDER) violated
                      John
                      • 8. Re: Missing right parent while creating table.
                        Frank Kulash
                        Hi,
                        1003332 wrote:
                        The solution in the first reply worked.
                        The problem was i think with both the 'reference statement' as well as the 'check condition'.
                        As pointed out in the solution the 'check(DELYDATE >= ORDERDATE)' constraint was referencing another column.
                        So i removed the check and it worked.

                        But i still don't know how to implement the "DELYDATE >= ORDERDATE" condition!
                        Would be helpful if someone could tell how?
                        You can find the syntax and examples in the SQL Language manual

                        A constraint that references more than 1 column can't be attached to one of the columns; it has to be defined as a table-level constraint, like the one at the end of this command:
                        create table SALES_ORDER
                        (
                            ORDERNO     varchar2 (6)     Primary key     
                                                     check (ORDERNO like 'O%'),
                            CLIENTNO     varchar2 (6)      references CLIENT_MASTER (CLIENTNO),
                            ORDERDATE     date                not null,
                            DELYADDR     varchar2 (25),
                            SALESMANNO     varchar2(6)     references SALESMAN_MASTER (SALESMANNO),
                            DELYTYPE     char (1)          default 'F'      
                                                          check (DELYTYPE IN ('P','F')),
                            BILLYN     char (1),
                            DELYDATE     date,
                            ORDERSTATUS varchar2 (10)     check (ORDERSTATUS IN ('In Process','Fulfilled','BackOrder','Cancelled')),
                            CONSTRAINT  sales_order_date_ck
                                            check (DELYDATE >= ORDERDATE)
                        );
                        As you can see, this can be done in the CREATE TABLE statement; use an ALTER TABLE statement only if you want to.
                        • 9. Re: Missing right parent while creating table.
                          bencol
                          That looks simpler than a virtual column!
                          • 10. Re: Missing right parent while creating table.
                            William Robertson
                            Nice.

                            So putting it all together, I make that:
                            -- Dummy tables for testing FKs:
                            create table client_master(clientno varchar2(6) constraint client_master_pk primary key)
                            organization index;
                            
                            create table salesman_master(salesmanno varchar2(6) constraint salesman_master_pk primary key)
                            organization index;
                            
                            create table sales_order
                            ( orderno      varchar2(6) primary key check(orderno like 'o%')
                            , clientno     constraint salesorder_client_fk references client_master
                            , orderdate    date not null
                            , delyaddr     varchar2(25)
                            , salesmanno   constraint salesorder_salesman_fk references salesman_master
                            , delytype     varchar2(1) default 'F' check(delytype in('P','F'))
                            , billyn       varchar2(1)
                            , delydate     date
                            , orderstatus  varchar2(10) check(orderstatus in('In Process','Fulfilled','BackOrder','Cancelled'))
                            , constraint dely_to_order_chk check(delydate >= orderdate) );
                            I corrected the CHARs to VARCHAR2, named the constraints and removed the datatypes form the FK columns to allow them to inherit from their parent.

                            I'm still concerned at the lack of NOT NULL constraints (is it really OK to have a sales order without a client or a status?) and the odd use of strings for key columns with <i>'no'</i> (i.e. 'number') in the name.

                            Edited by: William Robertson on Apr 30, 2013 3:10 PM
                            • 11. Re: Missing right parent while creating table.
                              BobLilly
                              (deleted -- too slow!)

                              Edited by: BobLilly on Apr 30, 2013 10:04 AM
                              • 12. Re: Missing right parent while creating table.
                                Smaipady
                                As per oracle docs
                                http://docs.oracle.com/cd/B19306_01/server.102/b14200/clauses002.htm

                                1. FOREIGN KEY clause is used for out-of-line specification only. For inline, "references" clause will workout.
                                2. As per oracle docs, check constraint specifies the below point.

                                "The syntax for inline and out-of-line specification of check constraints is the same. However, inline specification can refer only to the column (or the attributes of the column if it is an object column) currently being defined, whereas out-of-line specification can refer to multiple columns or attributes."

                                So, you can try this way
                                create table SALES_ORDER
                                (
                                ORDERNO varchar2(6) Primary key check(ORDERNO like 'O%'),
                                CLIENTNO varchar2(6) references CLIENT_MASTER(CLIENTNO),
                                ORDERDATE date not null,
                                DELYADDR varchar2(25),
                                SALESMANNO varchar2(6)  references SALESMAN_MASTER(SALESMANNO),
                                DELYTYPE char(1) default 'F' check(DELYTYPE IN('P','F')),
                                BILLYN char(1),
                                ORDERSTATUS varchar2(10) check(ORDERSTATUS IN('In Process','Fulfilled','BackOrder','Cancelled')),
                                DELYDATE date,
                                constraint dely_ord check(DELYDATE >= ORDERDATE)
                                );
                                Note: inline means specifying constraint immediately after column defination. out-of-line means at the table level.