can't set a foreign key on oracle sql-CodePudding

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.

CodePudding user response：

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

CodePudding user response：

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.