Missing keyword in Oracle SQL

Question

What's wrong with this query?

I am getting following error:

ORA-00905: missing keyword
00905. 00000 - "missing keyword"

My SQL:

CREATE Table ORDERDET
(
    ORDERID NUMBER,
    CUSTID NUMBER,
    PRODID NUMBER,
    ORDPRIORITY VARCHAR2(15),
    ORDDISCOUNT NUMBER(3,2),
    ORDSHIPMODE VARCHAR2(15),
    ORDDATE DATE,
    ORDSHIPDATE DATE,
    ORDSHIPCOST NUMBER(5,2),
    ORDQTY NUMBER,
    ORDSALES NUMBER(7,2),

    CONSTRAINT ch_ORDPRIORITY 
         CHECK (ORDPRIORITY IN ('Low', 'Medium', 'High',    'Critical', 'Not Specified')),
    CONSTRAINT ch_ORDSHIPMODE 
        CHECK (ORDSHIPMODE IN ('Regular Air','Delivery Truck','Express Air')),
    CONSTRAINT pk_ORDERDET 
        PRIMARY KEY (ORDERID, CUSTID, PRODID),

    CONSTRAINT fk_ORDERD 
        FOREIGN KEY (ORDERID) REFERENCES ORDERS (ORDERID) on DELETE RESTRICT,
    CONSTRAINT fk_CUSTOMERORDER 
        FOREIGN KEY (CUSTID) REFERENCES CUSTOMERS (CUSTID) on DELETE RESTRICT,
    CONSTRAINT fk_PRODUCTORDER 
        FOREIGN KEY (PRODID) REFERENCES PRODUCTS (PRODID) on DELETE RESTRICT
);

Is ORA-00905: missing keyword 00905. 00000 - "missing keyword" the entire message? Isn't there a line number as well? — Andy Lester
comment out all your constraints. try to create the table. Drop the table and try again adding one constraint at a time. Watch your commas and closing bracket. — Dan Bracuk
There is just a "Error starting at line : 641 in command -" from the start. — ANDIII324

krokodilko krokodilko · Accepted Answer · 2017-09-15T15:57:39

According to the documentation: CREATE TABLE
there is no on DELETE RESTRICT option, only CASCADE or SET NULL are allowed,
please see attached syntax diagram below:

I guess you want to prevent from deletion of a row in the parent table in a case when there are rows in the child table that references this parent row - if yes, then skip ON DELETE clause completely, because this is default behaviour of the foreign key constraint.

---------- EDIT ----------

Error report - ORA-02264: name already used by an existing constraint 02264. 00000 - "name already used by an existing constraint" *Cause: The specified constraint name has to be unique. *Action: Specify a unique constraint name for the constraint.

The error message says, that one of a constraint name you have used in the create table has already been creaded (used) and you cannot use it again.
I think you haven't showed us the whole error message, because Oracle should print this duplicate name.
Anyway, you can find which name is duplicate using this query:

select *
from user_objects
where object_name in (
'CH_ORDPRIORITY' ,
'CH_ORDSHIPMODE' ,
 'PK_ORDERDET' ,
'FK_ORDERD' ,
'FK_CUSTOMERORDER' ,
'FK_PRODUCTORDER'
)

If one (or a few) of these names are already used, then use a different name, say FK_PRODUCTORDER_11 instead of FK_PRODUCTORDER

Missing keyword in Oracle SQL

1 Answers