Creating a table with a foreign key

Question

Hey all I'm trying to create a table that contains a Foreign Key, and for some reason I am getting an error. The error says 00907. 00000 - "missing right parenthesis" which is odd because I don't have a random left parenthesis. I looked up how to create a table with a Foreign Key and that led to the following code:

Create Table EMPHIREINFO
(
empname VARCHAR2(10) NOT NULL FOREIGN KEY REFERENCES EMPADDRESS(empname),
empno NUMBER(4,0) NOT NULL PRIMARY KEY,
startdt DATE,
enddt DATE,
cntrlgth NUMBER(5,0)
)

I tried it with and without the REFERENCES EMPADDRESS(empname) and I still get the same error. Any help is appreciated, thanks.

Answer 1

You need to specify column after FOREIGN KEY. However, I'd prefer to use naming constraints, for instance

Create Table EMPHIREINFO
(
empname VARCHAR2(10) NOT NULL ,
empno NUMBER(4,0) NOT NULL ,
startdt DATE,
enddt DATE,
cntrlgth NUMBER(5,0),
CONSTRAINT PK_EMPHIREINFO PRIMARY KEY(empno) USING INDEX
 (CREATE UNIQUE INDEX IDXU_EMPHIREINFO_empno ON EMPHIREINFO(empno) ),
 CONSTRAINT FK_EMPHIREINFO_EMPNAME FOREIGN KEY(empname)
 REFERENCES EMPADDRESS(empname)
)