Home » SQL & PL/SQL » SQL & PL/SQL » ORA-00904: "SUPPID": invalid identifier (oracle )
ORA-00904: "SUPPID": invalid identifier [message #684154] Sat, 10 April 2021 13:06 Go to next message
whotheunknown
Messages: 1
Registered: April 2021
Junior Member
DROP TABLE BOAT ORA-00942: table or view does not exist -
4 0.00 DROP TABLE RENTAL ORA-00942: table or view does not exist -
20 0.00 CREATE TABLE BOAT (boat_id NUMBER(9) PRIMARY KEY,boatType VA ORA-00904: "SUPPID": invalid identifier
13 0.02 CREATE TABLE MAINTENANCE (MainID NUMBER(9) PRIMARY KEY,MainF ORA-00904: "BOAT_ID": invalid identifier
19 0.00 CREATE TABLE RENTAL (RentID NUMBER(9) PRIMARY KEY,RentDate D ORA-00904: "BOAT_ID": invalid identifier


i get these errors for invalid identifier and honestly I don't understand why, any help would be appreciated thanks
  • Attachment: groupA9.sql
    (Size: 3.44KB, Downloaded 102 times)
Re: ORA-00904: "SUPPID": invalid identifier [message #684155 is a reply to message #684154] Sat, 10 April 2021 13:48 Go to previous messageGo to next message
Michel Cadot
Messages: 67882
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

SQL> @C:\groupA9.sql
DROP TABLE SUPPLIERPARTS
           *
ERROR at line 1:
ORA-00942: table or view does not exist


DROP TABLE ORDERLINE
           *
ERROR at line 1:
ORA-00942: table or view does not exist


DROP TABLE BOAT
           *
ERROR at line 1:
ORA-00942: table or view does not exist


DROP TABLE RENTAL
           *
ERROR at line 1:
ORA-00942: table or view does not exist


DROP TABLE CLASSSIZE
           *
ERROR at line 1:
ORA-00942: table or view does not exist


DROP TABLE ORDERS
           *
ERROR at line 1:
ORA-00942: table or view does not exist


DROP TABLE PARTS
           *
ERROR at line 1:
ORA-00942: table or view does not exist


DROP TABLE SUPPLIER
           *
ERROR at line 1:
ORA-00942: table or view does not exist


DROP TABLE DEALER
           *
ERROR at line 1:
ORA-00942: table or view does not exist


DROP TABLE MAINTENANCE
           *
ERROR at line 1:
ORA-00942: table or view does not exist


DROP TABLE CUSTOMER
           *
ERROR at line 1:
ORA-00942: table or view does not exist



Table created.

FOREIGN KEY (boat_id) REFERENCES BOAT(boat_id))
             *
ERROR at line 11:
ORA-00904: "BOAT_ID": invalid identifier



Table created.


Table created.


Table created.


Table created.


Table created.

CONSTRAINT fk_rental_boat FOREIGN KEY (boat_id) REFERENCES BOAT(boat_id),
                                       *
ERROR at line 10:
ORA-00904: "BOAT_ID": invalid identifier


CONSTRAINT fk_boat_supplier FOREIGN KEY (SuppID) REFERENCES SUPPLIER(SuppID),
                                         *
ERROR at line 11:
ORA-00904: "SUPPID": invalid identifier



Table created.


Table created.
2 questions:
1/ What is your Oracle version?
2/ What client tool are you using?

The errors I have (If you ignore those on DROP at the beginning) comes from the fact that the referenced table is created AFTER the referencing one, just change the order of the CREATE TABLE statements.
Re: ORA-00904: "SUPPID": invalid identifier [message #684156 is a reply to message #684155] Sun, 11 April 2021 02:16 Go to previous messageGo to next message
Littlefoot
Messages: 21676
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Alternatively, create tables first, then primary keys, then foreign keys, e.g.

create table a (...);
create table b (...);

alter table a add constraint pka primary key ...;
alter table b add constraint pkb primary key ...;

alter table a add constraint fkab foreign key ... references b ...;
Re: ORA-00904: "SUPPID": invalid identifier [message #684157 is a reply to message #684156] Sun, 11 April 2021 08:36 Go to previous messageGo to next message
EdStevens
Messages: 1321
Registered: September 2013
Senior Member
Quote:
Alternatively, create tables first, then primary keys, then foreign keys, e.g.
Well, there would be no need to separate/delay the creation of the PK. Just the FK.

Whether one adjusts the order of the table creation or delays/separates the creation of the FK, the "key" to the solution is that the FK cannot be defined until after the referenced table (and its PK) are created.
Re: ORA-00904: "SUPPID": invalid identifier [message #684158 is a reply to message #684157] Mon, 12 April 2021 14:59 Go to previous messageGo to next message
Littlefoot
Messages: 21676
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
My idea was to either keep it all inline, or have it all outline.
Re: ORA-00904: "SUPPID": invalid identifier [message #684159 is a reply to message #684158] Mon, 12 April 2021 18:30 Go to previous message
EdStevens
Messages: 1321
Registered: September 2013
Senior Member
Littlefoot wrote on Mon, 12 April 2021 14:59
My idea was to either keep it all inline, or have it all outline.
I understand. I'm just always trying to make sure the OP understands the basic concept, not just a solution.
Razz
Previous Topic: Query to Display date
Next Topic: how to remove html tags from the text
Goto Forum:
  


Current Time: Tue Jun 15 16:49:52 CDT 2021