Making a table with a foreign key

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”

Answer

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.

CREATE TABLE STAFF(
EID INTEGER PRIMARY KEY,
NAME VARCHAR(50),
SURNAME VARCHAR(50),
HIREDATE DATE,
LID INTEGER
FOREIGN KEY (LID) REFERENCES LOC(LID));