SQL: ORA-00904: invalid identifier

Question

I am new to SQL and I get this error in Navicat for Oracle.

ORA-00904: : invalid identifier

 DROP TABLE Series CASCADE CONSTRAINTS;
DROP TABLE User1 CASCADE CONSTRAINTS;
DROP TABLE Following CASCADE CONSTRAINTS;
DROP TABLE Episode CASCADE CONSTRAINTS;
--DROP TABLE BUNGALOW CASCADE CONSTRAINTS;
--DROP TABLE Watched CASCADE CONSTRAINTS;



CREATE TABLE Series
(   SeriesID NUMBER(5) primary key,
    Name VARCHAR2(100) NOT NULL,
    Status VARCHAR2(15),
    Genre VARCHAR2(100),
    Country VARCHAR2(100),
    Network VARCHAR2(100),
    Runtime VARCHAR2(25)
);

CREATE TABLE User1(
  UserID NUMBER(5) primary key,
  Username VARCHAR2(100) NOT NULL,
    Country1 varchar2(100),
    Gender VARCHAR2(10) NOT NULL,
    Birthday VARCHAR2(100),
    Timezone VARCHAR2(100),
    About_me VARCHAR2(300),
    Password VARCHAR2(100) NOT NULL
);

CREATE TABLE Following (
    UserID NUMBER(5) NOT NULL,
    SeriesID NUMBER(5) NOT NULL,
    Ignore1 CHAR(1) NOT NULL,
    constraint Following_pk primary key(UserID, SeriesID),
    constraint Following_fk1 foreign key(UserID) REFERENCES User1(UserID),
    constraint Following_fk2 foreign key(SeriesID) REFERENCES Series(SeriesID)
);

CREATE TABLE Episode(
    SeriesID NUMBER(5) NOT NULL,
    Season NUMBER(2) NOT NULL,
    Episode NUMBER(2) NOT NULL,
    Name varchar2(100) NOT NULL,
    Airdate DATE,
    Summary varchar2(500),
    constraint episode_pk primary key(SeriesID, Season, Episode),
    constraint episode_fk foreign key(SeriesID) REFERENCES Series(SeriesID)
);

CREATE TABLE Watched(
    UserID NUMBER(5) NOT NULL,
    SeriesID NUMBER(5) NOT NULL,
    Season NUMBER(2) NOT NULL,
    Episode NUMBER(2) NOT NULL,
    constraint watched_pk primary key(UserID, SeriesID, Season, Episode),
    constraint watched_fk1 foreign key(UserID) REFERENCES User1(UserID),
    constraint watched_fk2 foreign key(SeriesID) REFERENCES Episode(SeriesID),
    constraint watched_fk3 foreign key(Season) REFERENCES Episode(Season),
    constraint watched_fk4 foreign key(Episode) REFERENCES Episode(Episode),
);

The error comes up when creating the Watched table. I guess it has something to do with the multiple foreign keys from the Episode-table but i can't seem to figure it out.

Przemyslaw Kruglej Przemyslaw Kruglej · Accepted Answer · 2013-11-04T20:43:24

You have a one comma to many, at the last constraint definition, try this:

  CREATE TABLE Watched(
    UserID NUMBER(5) NOT NULL,
    SeriesID NUMBER(5) NOT NULL,
    Season NUMBER(2) NOT NULL,
    Episode NUMBER(2) NOT NULL,
    constraint watched_pk primary key(UserID, SeriesID, Season, Episode),
    constraint watched_fk1 foreign key(UserID) REFERENCES User1(UserID),
    constraint watched_fk4 foreign key(Episode, season, seriesid) REFERENCES Episode(SeriesID, Season, Episode)
);

Also, you had too many foreign keys - instead of 3 foreign keys to reference the EPISODE table, you should have only one with 3 columns.

SQL: ORA-00904: invalid identifier

1 Answers