CODEWITHSUNDEEP
sqloracle-database
Guibz
Guibz

Reputation: 11

SQL : ORA-00906: missing left parenthesis

I met a problem with CREATE TABLE:

Here is my instruction :

CREATE TABLE PRATICIEN (
num_pra INTEGER PRIMARY KEY,
nom_pra VARCHAR(30) NOT NULL,
FOREIGN KEY code_etage REFERENCES ETAGE(code_etage));

ORA-00906: missing left parenthesis 00906. 00000 - "missing left parenthesis"

ETAGE exists and this table was created without error :

CREATE TABLE ETAGE (
code_etage SMALLINT PRIMARY KEY,
designation VARCHAR(30));

Upvotes: 1

Views: 2381

Answers (3)

William Robertson
William Robertson

Reputation: 15991

Simplest would be

create table praticien
( num_pra    integer primary key
, nom_pra    varchar2(30) not null
, code_etage references etage );

However, specifying the referenced column might be considered best practice, in case ETAGE has more than one unique constraint:

create table praticien
( num_pra    integer primary key
, nom_pra    varchar2(30) not null
, code_etage references etage(code_etage) );

(By the way, note that it's varchar2 in Oracle, not varchar.)

Upvotes: 1

Lukasz Szozda
Lukasz Szozda

Reputation: 175586

You could simply remove FOREIGN KEY keyword:

CREATE TABLE PRATICIEN (
    num_pra INTEGER PRIMARY KEY,
    nom_pra VARCHAR(30) NOT NULL,
    code_etage REFERENCES ETAGE(code_etage)  -- please note that type is inferred
);

db<>fiddle demo

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269513

You need parentheses around the foreign key reference. That is how the syntax is defined.

You also need to declare the column. The FOREIGN KEY is an attribute of a column, not a column definition:

CREATE TABLE PRATICIEN (
    num_pra INTEGER PRIMARY KEY,
    nom_pra VARCHAR(30) NOT NULL,
    code_etage SMALLINT,
    FOREIGN KEY (code_etage) REFERENCES ETAGE(code_etage)
);

Here is a db<>fiddle.

Upvotes: 3

Related Questions