Missing key error but I cannot find what is the missing key

Question

I am using ORACLE DBMS and the Department table for referencing has already been created. I am getting the below error for my code.

Error starting at line : 6 in command -
Error report -
ORA-00905: missing keyword
00905. 00000 - "missing keyword"
*Cause:
*Action:

My code is as follows:

CREATE TABLE STAFF
(StaffID VARCHAR(3),
GivenName VARCHAR(20) NOT NULL,
FamilyName VARCHAR(20) NOT NULL,
DateOfBirth DATE,
Email VARCHAR(50) NOT NULL UNIQUE,
Mobile VARCHAR(10),
State VARCHAR(3),
Salary NUMBER(4,2) NOT NULL,
DepartmentNo CHAR(4),
CONSTRAINT staffpk PRIMARY KEY (StaffID),
CONSTRAINT chkgivenname CHECK (GivenName LIKE '%[^A-Z]%'),
CONSTRAINT chkfamilyname CHECK (FamilyName LIKE '%[^A-Z]%'),
CONSTRAINT chkdate CHECK (DateOfBirth >= '01/01/1900'),
CONSTRAINT chkemail CHECK (Email LIKE '%_@__%.__%'),
CONSTRAINT chkmobile CHECK (Mobile LIKE '%[^0-9]%'),
CONSTRAINT chkstate CHECK (State IN ('WA','VIC','QLD','SA','NSW','TAS','NT','ACT')),
CONSTRAINT stafffk FOREIGN KEY (DepartmentNo)
        REFERENCES DEPARTMENT(DepartmentNo)
        ON UPDATE CASCADE);

You [asked this earlier](https://stackoverflow.com/q/71305252/266304), and it was closed as a duplicate of [this question](https://stackoverflow.com/q/35002114/266304). Not sure why you deleted that and asked the same thing again. The answer is the same as it was yesterday - Oracle doesn't have `on update cascade`. — Alex Poole, Mar 01 '22 at 12:03
A couple of side observations unrelated to your error. First, in oracle you should be using VARCHAR2 instead of VARCHAR. Second, in oracle object names are treated as case in-sensitive by default, but are stored in the data dictionary in all caps. So while you can name a column GivenName, and refer to it as GivenName, it will be recorded in the dictionary as GIVENNAME. So most oracle practitioners do not use CamelCase, but rather underscore delimiters. So instead of GivenName, will use given_name. — EdStevens, Mar 01 '22 at 15:11

score 0 · Answer 1 · answered Mar 01 '22 at 09:53

There is no ON UPDATE CASCADE in Oracle.

Here is a small demo showing your error and the code without error: DEMO

Please note that I have added a dummy DEPARTMENT table so this can work and also I have changed your date from '01/01/1900' to '01/MAR/1900' for the same purpose.

I have now founded also a similar problem already posted here: ORA-00905: missing keyword (constraint foreign key)

Missing key error but I cannot find what is the missing key

1 Answers1