I'm creating a SQL database in Oracle. I have created this table:
CREATE TABLE LOC
(
LID INTEGER PRIMARY KEY,
CITY VARCHAR(50),
STREET VARCHAR(50),
SNUMBER VARCHAR(50)
);
And I tried creating a second table that has a foreign key referencing it:
CREATE TABLE STAFF
(
EID INTEGER PRIMARY KEY,
NAME VARCHAR(50),
SURNAME VARCHAR(50),
HIREDATE DATE,
FOREIGN KEY (LID) REFERENCES LOC(LID)
);
I get this error:
ORA-00904: "LID": invalid identifier
00904. 00000 - "%s: invalid identifier"
NickW :
Defining a foreign key on a column doesn't create the column. You need to create the column first and then define it as a foreign key.\nCREATE TABLE STAFF(\nEID INTEGER PRIMARY KEY,\nNAME VARCHAR(50),\nSURNAME VARCHAR(50),\nHIREDATE DATE,\nLID INTEGER\nFOREIGN KEY (LID) REFERENCES LOC(LID));\n",
2021-06-17T18:21:59