CODEWITHSUNDEEP
sqldatabaseoracle-database
Taylor Styles
Taylor Styles

Reputation: 178

Error ORA-00904 in SQL developer. Seemly no fix online that works that I can find while creating a table

I have seen many posts regarding the seemingly infamous ORA-00904 yet nothing seems to be helping. I am creating 2 tables (for now, more to come later) which are as follows;

CREATE TABLE Employee(
    EmployeeID int NOT NULL,
    PRIMARY KEY(EmployeeID),
    customerID INT NOT NULL,
    CONSTRAINT employee_customer_fk FOREIGN KEY (customerID) REFERENCES Customer(CustomerID),
    LastName CHAR(20) NOT NULL,
    MiddleInitial CHAR(1), 
    FirstName CHAR(20) NOT NULL, 
    Region CHAR(20) NOT NULL,
    DateOfHire VARCHAR(20) NOT NULL,
    Skill VARCHAR(50) NOT NULL
);

and

CREATE TABLE Customer(
    CustomerID INT NOT NULL PRIMARY KEY,
    ProjectID INT NOT NULL,
    FOREIGN KEY(ProjectID) REFERENCES Project(ProjectID), /*This is yet another table with a foreign key which needs to be sorted*/
    CustomerName Char(255) NOT NULL,
    PhoneNumber INT NOT NULL,
    Region CHAR(255) NOT NULL
);

but every time I run it, I get error;

Error starting at line : 4 in command -
CREATE TABLE Employee(
    EmployeeID int NOT NULL,
    PRIMARY KEY(EmployeeID),
    customerID INT NOT NULL,
    CONSTRAINT employee_customer_fk FOREIGN KEY(customerID) REFERENCES Customer(CustomerID),
    LastName CHAR(20) NOT NULL,
    MiddleInitial CHAR(1), 
    FirstName CHAR(20) NOT NULL, 
    Region CHAR(20) NOT NULL,
    DateOfHire VARCHAR(20) NOT NULL,
    Skill VARCHAR(50) NOT NULL
)
Error report -
ORA-00904: "CUSTOMERID": invalid identifier
00904. 00000 -  "%s: invalid identifier"
*Cause:    
*Action:

ANY HELP WOULD BE GREATLY APPRECIATED!

Upvotes: 0

Views: 133

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269773

The referenced table needs to created before the foreign key reference. One method is to create the tables first and then add the foreign key references:

CREATE TABLE Employee (
    EmployeeID int NOT NULL,
    PRIMARY KEY (EmployeeID),
    customerID INT NOT NULL,
    LastName CHAR(20) NOT NULL,
    MiddleInitial CHAR(1), 
    FirstName CHAR(20) NOT NULL, 
    Region CHAR(20) NOT NULL,
    DateOfHire VARCHAR(20) NOT NULL,
    Skill VARCHAR(50) NOT NULL
);

CREATE TABLE Customer (
    CustomerID INT NOT NULL PRIMARY KEY,
    ProjectID INT NOT NULL,
    -- FOREIGN KEY(ProjectID) REFERENCES Project(ProjectID), /*This is yet another table with a foreign key which needs to be sorted*/
    CustomerName Char(255) NOT NULL,
    PhoneNumber INT NOT NULL,
    Region CHAR(255) NOT NULL
);

alter table Employee add CONSTRAINT employee_customer_fk FOREIGN KEY (customerID) REFERENCES Customer (CustomerID)

Here is a db<>fiddle.

Note that I commented out the foreign key constraint to Project.

Also, in most data models you can just order the table creates -- there are no cycles in the foreign key "linkages". However, because you have not defined Project, I am suggesting this more general approach.

Upvotes: 1

Vivien Sonntag
Vivien Sonntag

Reputation: 4629

Oracle by default converts all names to UpperCase if you don't enquote them. (Edit: At the time of this answer the Foreign Key Constraint in the starting post did contain a quoted column name).

So, your constraint tries to find a column named "customerID" whereas the real name of your column is "CUSTOMERID".

Either enquote your column:

CREATE TABLE Employee(
    EmployeeID int NOT NULL,
    PRIMARY KEY(EmployeeID),
    "customerID" INT NOT NULL,
    CONSTRAINT employee_customer_fk FOREIGN KEY ("customerID") REFERENCES customer(CustomerID),
    LastName CHAR(20) NOT NULL,
    MiddleInitial CHAR(1), 
    FirstName CHAR(20) NOT NULL, 
    Region CHAR(20) NOT NULL,
    DateOfHire VARCHAR(20) NOT NULL,
    Skill VARCHAR(50) NOT NULL
)

Don't use quotes on your FOREIGN KEY constraint:

CREATE TABLE Employee(
    EmployeeID int NOT NULL,
    PRIMARY KEY(EmployeeID),
    customerID INT NOT NULL,
    CONSTRAINT employee_customer_fk FOREIGN KEY (customerID) REFERENCES customer(CustomerID),
    LastName CHAR(20) NOT NULL,
    MiddleInitial CHAR(1), 
    FirstName CHAR(20) NOT NULL, 
    Region CHAR(20) NOT NULL,
    DateOfHire VARCHAR(20) NOT NULL,
    Skill VARCHAR(50) NOT NULL
)

Or use quotes and write it uppercase in your foreign key constraint:

CREATE TABLE Employee(
    EmployeeID int NOT NULL,
    PRIMARY KEY(EmployeeID),
    customerID INT NOT NULL,
    CONSTRAINT employee_customer_fk FOREIGN KEY ("CUSTOMERID") REFERENCES customer(CustomerID),
    LastName CHAR(20) NOT NULL,
    MiddleInitial CHAR(1), 
    FirstName CHAR(20) NOT NULL, 
    Region CHAR(20) NOT NULL,
    DateOfHire VARCHAR(20) NOT NULL,
    Skill VARCHAR(50) NOT NULL
)

As already mentioned, keeping the style consistent is recommended to prevent future problems, so Option 2 would be the easiest solution. Otherwise you need to enquote all columns to be consistent.

/Edit: I just tried it - this definitely works with Oracle 12C:

CREATE TABLE Customer (
CustomerID INT,
PRIMARY KEY (CustomerID)
)

Followed by:

CREATE TABLE Employee(
    EmployeeID int NOT NULL,
    PRIMARY KEY(EmployeeID),
    customerID INT NOT NULL,
    CONSTRAINT employee_customer_fk FOREIGN KEY(customerID) REFERENCES Customer(CustomerID),
    LastName CHAR(20) NOT NULL,
    MiddleInitial CHAR(1), 
    FirstName CHAR(20) NOT NULL, 
    Region CHAR(20) NOT NULL,
    DateOfHire VARCHAR(20) NOT NULL,
    Skill VARCHAR(50) NOT NULL
)

Upvotes: 1

Related Questions