Ichiban Ichiban - 13 days ago 5
SQL Question

Invalid identifier Oracle SQL Dev

I have a stupid problem, I know, but I don't see my error(s) ...

Here is my SQL code when I create my tables

CREATE TABLE Nageur
( NumN number not null,
NomN varchar2(30) null,
PrenomN varchar2(30) null,
AnDateNaiss number null,
Nationalite varchar2(10),
Genre varchar2(1),
Pole varchar2(50),
CONSTRAINT PK_Nageur PRIMARY KEY (NumN));

CREATE INDEX I_FK_Nageur_Club
ON Nageur (NumClub ASC);

CREATE TABLE Club
( NumClub number NOT NULL,
NomClub varchar2(40) NULL,
Region varchar2(40) NULL,
Departement varchar2(40) NULL,
CONSTRAINT PK_Club PRIMARY KEY (NumClub));


The lines that gives me errors

ALTER TABLE Nageur ADD (
CONSTRAINT FK_nageur_club
FOREIGN KEY (NumClub)
REFERENCES Club (NumClub));

ALTER TABLE Competition ADD (
CONSTRAINT FK_competition_piscine
FOREIGN KEY (NumP)
REFERENCES Piscine (NumP));


And here my errors on Oracle

Erreur commençant à la ligne: 97 de la commande -
ALTER TABLE Nageur ADD (
CONSTRAINT FK_nageur_club
FOREIGN KEY (NumClub)
REFERENCES Club (NumClub))
Rapport d'erreur -
Erreur SQL : ORA-00904: "NUMCLUB" : identificateur non valide
00904. 00000 - "%s: invalid identifier"
*Cause:

*Action:

Erreur commençant à la ligne: 102 de la commande -
ALTER TABLE Competition ADD (
CONSTRAINT FK_competition_piscine
FOREIGN KEY (NumP)
REFERENCES Piscine (NumP))
Rapport d'erreur -
Erreur SQL : ORA-00904: "NUMP" : identificateur non valide
00904. 00000 - "%s: invalid identifier"
*Cause:

*Action:

And i don't see my error because NumClub and NumP exist...

Thanks

Answer

You are missing the column NumClub in the table Nageur.

Once fixed this, all the statements, except the one on Competition , should run fine; the statement on Competition can not be verified, not knowing the structure of this table.