can't set a foreign key on oracle sql

Question

Here is my code:

SQL> CREATE TABLE manufacturer(
  manufacturer_id integer primary key,
  manufacturer_name varchar2(20)
);

Table created.

SQL> CREATE TABLE models(
  model_id integer primary key,
  model_name varchar2(20),
  model_year integer,
  eng_cylinder integer,
  eng_horsepower integer,
  manufacturer_id int foreign key references manufacturer(manufacturer_id)
);

and it gave me this error:

ERROR at line 1:
ORA-00907: missing right parenthesis

What is wrong with parenthesis in my code? this only happens when i set foreign key.

Note: i am using SQL cmd from Oracle Database 11g Express Edition.

I've tried making theses tables on MySQL and it worked fine.

Related: https://stackoverflow.com/questions/24580780/ora-00907-missing-right-parenthesis It says that this problem is often a generic syntax error, not actually mismatched parentheses. — Barmar, May 10 '22 at 18:54

score 1 · Accepted Answer · answered May 10 '22 at 18:53

No "FOREIGN KEY" keyword is used for inline constraints. So, like this:

CREATE TABLE manufacturer (manufacturer_id integer primary key, manufacturer_name varchar2(20));

CREATE TABLE models (model_id integer primary key, 
                     model_name varchar2(20),
                     model_year integer, 
                     eng_cylinder integer, 
                     eng_horsepower integer,
                     manufacturer_id int constraint models_fk1 references manufacturer(manufacturer_id));

score 1 · Answer 2 · answered May 10 '22 at 18:58

Right, just as Matthew already said.

Alternatively, create the foreign key constraint out of line - then you do use the foreign key part of the syntax (see line #9):

SQL> CREATE TABLE models
  2    (model_id integer primary key,
  3     model_name varchar2(20),
  4     model_year integer,
  5     eng_cylinder integer,
  6     eng_horsepower integer,
  7     manufacturer_id int,
  8     --
  9     constraint models_fk1 foreign key (manufacturer_id) references manufacturer(manufacturer_id)
 10    );

Table created.

SQL>

Or, yet another option - alter table:

SQL> alter table models add constraint models_fk1 foreign key
  2    (manufacturer_id) references manufacturer (manufacturer_id);

Table altered.

This is actually the way I prefer - all constraints (except NOT NULL) out of line. I answered with the inline constraint because that seemed to be the way the OP was going. But this is a good addition I should have mentioned in my answer. — Matthew McPeak, May 11 '22 at 13:40

can't set a foreign key on oracle sql

2 Answers2