CODEWITHSUNDEEP
sqldatabaseoracle-database
Ppp Lll
Ppp Lll

Reputation: 15

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"

Upvotes: 0

Views: 131

Answers (2)

William Robertson
William Robertson

Reputation: 15991

It needs to be either a column definition plus a full constraint definition, or (better in my view) name the column, say what it references and let it inherit the datatype.

(By the way, what's varchar?)

create table loc
( lid       integer primary key
, city      varchar2(50)
, street    varchar2(50)
, snumber   varchar2(50) );

create table staff
( eid       integer primary key
, name      varchar2(50)
, surname   varchar2(50)
, hiredate  date
, lid       references loc(lid) );

Upvotes: 0

NickW
NickW

Reputation: 9768

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));

Upvotes: 2

Related Questions