SQL error code ORA-00904: : invalid identifier 00904. 00000 - "%s: invalid identifier" when creating a table with constraints

Question

When creating the table below I get error code

ORA-00904: invalid identifier 00904. 00000 - "%s: invalid identifier"

CREATE TABLE Staff (
    staffNo     VARCHAR2(5),
    fName       VARCHAR2(10),
    lName       VARCHAR2(10),
    position    VARCHAR2(10),
    DOB         DATE,
    salary      NUMBER(7,2)        NOT NULL,
    branchNo    CHAR(4),
    supervisor  VARCHAR2(5),
    CONSTRAINT Staff_PK     PRIMARY KEY (staffNo),
    CONSTRAINT Staff_AK     UNIQUE      (fName, lName, branchNo),
    CONSTRAINT Staff_FK     FOREIGN KEY     (branchNo) REFERENCES Branch (branchNo),
    CONSTRAINT Staff_pos    CHECK       (position IN ('Manager', 'Supervisor')),
    CONSTRAINT Staff_fName  CHECK       (NOT (fname IS NULL)),
    CONSTRAINT Staff_lName  CHECK       (lName IS NOT NULL),
    CONSTRAINT Staff_branch CHECK       (branchNo IS NOT NULL),
    CONSTRAINT Staff_position <> 'manager' OR 'supervisor' IS NULL,
    CONSTRAINT 'A staff member can supervise up to 10 others.',
    CONSTRAINT 'A staff member who supervises others is in the position of supervisor or 
        manager'
);

I think the issue may have to do with the constraints, but I am not sure. This is my first time coding and using Oracle. Any help/ guidance would be very much appreciated.

Answer 1

Do you create table BRANCH before trying to create table STAFF? If yes then this should work. If No then create the table BRANCH first of remove CONSTRAINT Staff_FK FOREIGN KEY (branchNo) REFERENCES Branch (branchNo) from STAFF table definition and STAFF table will be created. You can add CONSTRAINT Staff_FK FOREIGN KEY (branchNo) REFERENCES Branch (branchNo) using ALTER TABLE STAFF statement after you create BRANCH TABLE.

"REFERENCES Branch (branchNo)" needs the table branch for referential integrity, and It is better to define a column NOT NULL instead of using CHECK to avoid NULLS.

CREATE TABLE Staff (
    staffNo     VARCHAR2(5),
    fName       VARCHAR2(10) NOT NULL ,
    lName       VARCHAR2(10) NOT NULL ,
    position    VARCHAR2(10) NOT NULL ,
    DOB         DATE,
    salary      NUMBER(7,2)        NOT NULL,
    branchNo    CHAR(4) NOT NULL ,
    supervisor  VARCHAR2(5),
    CONSTRAINT Staff_PK     PRIMARY KEY (staffNo),
    CONSTRAINT Staff_AK     UNIQUE      (fName, lName, branchNo),
    CONSTRAINT Staff_FK     FOREIGN KEY     (branchNo) REFERENCES Branch (branchNo),
    CONSTRAINT Staff_pos  CHECK( position IN ('Manager', 'Supervisor')),
    CONSTRAINT Staff_position CHECK (position <> 'manager' OR supervisor IS NULL)

    /* CONSTRAINT 'A staff member can supervise up to 10 others.',
    CONSTRAINT 'A staff member who supervises others is in the position of supervisor or manager' -- you need a trigger for these)*/
);

For this 'A staff member can supervise up to 10 others.' you can not create something directly in the table definition. You may try using a trigger to retrict the number of staff members to 10 under a supervisor.