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

I'm trying to figure out what I'm doing wrong. I have two related tables. I'm creating them in the following order, which I'm almost sure is correct. The problem is that I'm unable to create first table "vod_classification" because I'm receiving an error:

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

CREATE TABLE vod_classification (
dbClassId CHAR(4),
dbDescription VARCHAR2(90),
CONSTRAINT vod_classification_PK PRIMARY KEY (dbClassId),
REFERENCES vod_film (dbClass)
);

CREATE TABLE vod_film (
dbFilmId               NUMBER(4) NOT NULL,
dbTitle                VARCHAR2(50) NOT NULL,
dbDirector_firstname   VARCHAR2(10) NOT NULL,
dbDirector_lastname    VARCHAR2(10) NOT NULL,
dbGenre                VARCHAR2(10),
dbUK_release_date      DATE,
dbFilename             VARCHAR2 (15),
dbRuntime              NUMBER (10),
dbClass                CHAR(8),
CONSTRAINT vod_film_PK PRIMARY KEY (dbFilmId),
CONSTRAINT vod_film_class_FK
FOREIGN KEY (dbClass)
REFERENCES vod_classification (dbClassId)
ON DELETE SET NULL
);

2 answers

  • answered 2018-01-14 08:30 lad2025

    PRIMARY KEY does not have REFERENCES part:

    enter image description here

    CREATE TABLE vod_classification (
    dbClassId CHAR(4),
    dbDescription VARCHAR2(90),
    CONSTRAINT vod_classification_PK PRIMARY KEY (dbClassId)
    --REFERENCES vod_film (dbClass)
    );
    

    DBFiddle Demo

  • answered 2018-01-14 10:23 Littlefoot

    You are doing something wrong, but the question is what.

    It is easy to remove the REFERENCES clause from the VOD_CLASSIFICATION table if it doesn't belong there. However, it appears that you intentionally put it as PRIMARY KEY constraint ends with a comma, so it might mean that you really meant to create a foreign key from the VOD_CLASSIFICATION table to VOD_FILM.

    If that's so, you should switch places for those two tables because you can't create a foreign key constraint which references a table that hasn't been created yet.

    On the other hand, VOD_FILM also references the VOD_CLASSIFICATION table. This is not a very usual situation, where two tables interchange foreign key constraints on the same column, i.e. both of them act as masters and details. Oracle will let you do that, but - column you reference should be primary or unique key (and VOD_FILM.DBCLASS is not). Moreover, foreign key constraints should be created using the ALTER TABLE commands.

    Here's an example:

    SQL> create table vod_classification
      2    (dbclassid     varchar2(4) constraint pk_class primary key,
      3     dbdescription varchar2(90)
      4    );
    
    Table created.
    
    SQL> create table vod_film
      2    (dbfilmid number (4), -- move PRIMARY KEY to DBCLASS constraint pk_film primary key,
      3     dbclass  varchar2(4) constraint pk_film primary key
      4    );
    
    Table created.
    
    SQL> alter table vod_classification add constraint
      2    fk_class_film foreign key (dbclassid)
      3    references vod_film (dbclass);
    
    Table altered.
    
    SQL> alter table vod_film add constraint
      2    fk_film_class foreign key (dbclass)
      3    references vod_classification (dbclassid);
    
    Table altered.
    
    SQL>
    

    In such a case, you can't even drop tables (detail first, master next) as both of them are master and detail, so you'd have to drop foreign key constraints first, then the tables.

    Is it what you really want? I don't think so.

    Therefore, I suppose that REFERENCE clause you used in VOD_CLASSIFICATION is just a mistake and you should remove it.

    Furthermore, is there any reason for using CHAR data type? You'd rather use VARCHAR2.

    Also, you should match data types in both tables; VOD_CLASSIFICATION.DBCLASSID is CHAR(4), while VOD_FILM.DBCLASS (which references the DBCLASSID) is CHAR(8).

    I've tried to fix those mistakes so here's how you should have done it.

    SQL> CREATE TABLE vod_classification (
      2    dbClassId VARCHAR2(4)      CONSTRAINT pk_class PRIMARY KEY,
      3    dbDescription VARCHAR2(90)
      4  );
    
    Table created.
    
    SQL> CREATE TABLE vod_film (
      2    dbFilmId               NUMBER(4) NOT NULL
      3                               CONSTRAINT pk_film PRIMARY KEY,
      4    dbTitle                VARCHAR2(50) NOT NULL,
      5    dbDirector_firstname   VARCHAR2(10) NOT NULL,
      6    dbDirector_lastname    VARCHAR2(10) NOT NULL,
      7    dbGenre                VARCHAR2(10),
      8    dbUK_release_date      DATE,
      9    dbFilename             VARCHAR2 (15),
     10    dbRuntime              NUMBER (10),
     11    dbClassId              VARCHAR2(4)
     12                               CONSTRAINT fk_film_class
     13                                 REFERENCES vod_classification (dbclassid)
     14                                 ON DELETE SET NULL
     15  );
    
    Table created.
    
    SQL>
    

    So much fuss about a pure, little ", REFERENCES vod_film (dbClass)" line ...