Right Parenthesis Error?

Question

create table sales_order(
    order_no varchar2(6) Primary Key check(order_no like 'O%'),
    order_date date,
    client_no varchar2(6) foreign key references client_master(client_no),
    dely_address varchar2(25),
    salesman_no varchar2(6) foreign key references salesman_master(salesman_no),
    dely_type char(1) default 'F' check(dely_type like 'F' OR dely_type like 'P'),
    billed_yn char(1),
    dely_date date check (dely_date > order_date),
    order_status varchar2(10) check(order_status IN ('In Process','Fulfilled','BackOrder','Cancelled'))
);

When I am trying to execute this query it displays

ORA-00907: missing right parenthesis. Don't know where right parenthesis is missing.

That is the default error message when Oracle has no idea what is wrong. :-) — juergen d, Apr 28 '18 at 06:14
There is one simple solution. Comment all lines and uncomment one by one until you get error. Then fix it and do it until you get entire table definition. — Lukasz Szozda, Apr 28 '18 at 06:16
This table definition has multiple flaws. Anyway working **[DEMO](https://dbfiddle.uk/?rdbms=oracle_11.2&fiddle=31cef3d233a65881307bb65846666acd)** (removing `foreign key` when you use inline syntax without explicit name) — Lukasz Szozda, Apr 28 '18 at 06:23
Unrelated, but: `check(dely_type like 'F' OR dely_type like 'P')` can be simplified to `check(dely_type in ('F', 'P'))` — , Apr 28 '18 at 06:31
Unrelated, but those `char` columns should be `varchar2` which is the standard string type in Oracle. https://stackoverflow.com/a/42165653/230471 — William Robertson, Apr 28 '18 at 10:02

score 0 · Accepted Answer · answered Apr 28 '18 at 06:31

Use this query that has three constraints moved to the end:

    create table sales_order(
    order_no varchar2(6) Primary Key check(order_no like 'O%'),
    order_date date,
    client_no varchar2(6) ,
    dely_address varchar2(25),
    salesman_no varchar2(6) ,
    dely_type char(1) default 'F' check(dely_type like 'F' OR dely_type like 'P'),
    billed_yn char(1),
    dely_date date,
    order_status varchar2(10) check(order_status IN ('In Process','Fulfilled','BackOrder','Cancelled')),
    constraint sales_fk foreign key(client_no) references client_master(client_no),
    constraint salesman_fk foreign key(salesman_no) references salesman_master(salesman_no),
    constraint dely_date_ck check (dely_date > order_date)
);

If you move FK definitions to the end, you have to define datatypes for the FK columns. In my opinion it is better practice to let them inherit from the parent. — William Robertson, Apr 28 '18 at 09:54

Right Parenthesis Error?

1 Answers1