ORA-00907: missing right parenthesis

Question

CREATE TABLE Persons (
  P_Id int NOT NULL,
  LastName varchar(255) NOT NULL,
  FirstName varchar(255),
  PRIMARY KEY (P_Id)
)

CREATE TABLE Orders (
  O_Id int NOT NULL PRIMARY KEY,
  OrderNo int NOT NULL,
  P_Id int FOREIGN KEY REFERENCES Persons(P_Id)
)

I am getting an error while creating Table Orders:

ORA-00907: missing right parenthesis

leave the two words FOREIGN KEY out. Either you use FOREIGN KEY as a separate clause (separated by a comma) and give a field list, or use REFERENCES at the end of an existing field. See Oracle SQL Reference. — mihi
Is there an online tool to help clear up these kinds of errors? My code runs in SQL Developer and it validates in put Cognos 10 report studio, but when I try to generate the SQL it gives this error. I'm guessing it isn't actually caused by parens, but is there a tool to help sort this out? — Rick Henderson
Also found this from the IBM Knowledge Center - ORA-00907 may also be caused with queries that use left outer joins and on clauses: ibm.com/support/knowledgecenter/SSMR4U_10.1.0/… — Rick Henderson

Chandu Chandu · Accepted Answer · 2011-07-25T17:21:27

If you are defining a foreign key inline with column definition then you shouldn't specify FOREIGN KEY. Drop it from the definition.

Try this:

CREATE TABLE Orders 
( 
  O_Id int NOT NULL PRIMARY KEY, 
  OrderNo int NOT NULL,
  P_Id int REFERENCES Persons(P_Id)
)

ORA-00907: missing right parenthesis

1 Answers