Home » SQL & PL/SQL » SQL & PL/SQL » ORA-00904: invalid identifier error with Foreign Keys (Oracle iSQL Plus)
ORA-00904: invalid identifier error with Foreign Keys [message #310750] Wed, 02 April 2008 05:11 Go to next message
samwij
Messages: 2
Registered: April 2008
Location: Melbourne, Australia
Junior Member
Hi, I am new to SQL and am doing a course in Relational Databases. I am creating a series of tables however I get 'Invalid Identifier' messages when creating my tables with a Foreign Key. If someone could shed some lights on this issue it would be greatly appreciated:

The Foreign Key - TEAMNO is invalid but I dont know why:

CREATE TABLE TEAM
(
TeamNo NUMBER(4,1) NOT NULL,
TeamName VARCHAR2(30),
PRIMARY KEY (TeamNo)
);

CREATE TABLE PURCHASER
(
PFirstName VARCHAR2(30) NOT NULL,
PSurname VARCHAR2(30) NOT NULL,
Address VARCHAR2(3) NOT NULL,
PRIMARY KEY (PFirstName, PSurname)
);

CREATE TABLE INFORMATION_SESSION
(
SessNo NUMBER(4,1),
SessTitle VARCHAR2(30) NOT NULL,
Duration VARCHAR2(30) NOT NULL,
Price NUMBER(4,2) NOT NULL,
PRIMARY KEY (SessNo),
FOREIGN KEY (TeamNo) REFERENCES TEAM
);


CREATE TABLE STUDENT
(
StuId NUMBER(4,1),
SFirstName VARCHAR2(30) NOT NULL,
SSurname VARCHAR2(30) NOT NULL,
Gender VARCHAR(30) NOT NULL,
PRIMARY KEY (StuId),
FOREIGN KEY (TeamNo) REFERENCES TEAM);

CREATE TABLE TICKET
(
TicketNumber NUMBER(4,1),
PRIMARY KEY (TicketNumber),
FOREIGN KEY (SessNo) REFERENCES INFORMATION_SESSION,
FOREIGN KEY (PFirstName, PSurname) REFERENCES PURCHASER
);

Re: ORA-00904: invalid identifier error with Foreign Keys [message #310753 is a reply to message #310750] Wed, 02 April 2008 05:30 Go to previous messageGo to next message
dhananjay
Messages: 635
Registered: March 2002
Location: Mumbai
Senior Member
You get the errors because
TABLE INFORMATION_SESSION
Quote:
FOREIGN KEY (TeamNo) REFERENCES TEAM

you don't have any field by name Teamno in the above table.Same way check for other tables also.

And next time format your code.


regards,
Re: ORA-00904: invalid identifier error with Foreign Keys [message #310756 is a reply to message #310750] Wed, 02 April 2008 05:35 Go to previous messageGo to next message
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Next time, please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter) and align the columns in result.
Use the "Preview Message" button to verify.
Always post your Oracle version (4 decimals).

Regards
Michel

[Updated on: Wed, 02 April 2008 05:36]

Report message to a moderator

Re: ORA-00904: invalid identifier error with Foreign Keys [message #310765 is a reply to message #310756] Wed, 02 April 2008 06:02 Go to previous message
samwij
Messages: 2
Registered: April 2008
Location: Melbourne, Australia
Junior Member
Thanks for your help. and apologies for not posting in the right format.
Previous Topic: is possible to make Virus via Oracle !
Next Topic: Autogenerated ID
Goto Forum:
  


Current Time: Thu Feb 06 14:29:00 CST 2025