Hello, I am trying to run sql code but i keep getting errors in different stages i dont know where i-CodePudding

CREATE TABLE Publishers(
  Publisher_id varchar2(40),
  Publisher_Name Varchar2(40),
  Countries Varchar2(40),
  Addresses VarChar2(40),
  Phone Number(10,0),
  Emails Varchar2(40),
  Journals Varchar2(40),
  constraint pk_Publisher_id PRIMARY KEY (Publisher_id)
);

CREATE TABLE ScientificJournal(
  Journal_ID Varchar2(40),
  JournalName Type,
  Issues Type,
  Volumes Number(10,0),
  Editor Varchar2(40),
  Publisher_id Varchar2(40),
  ISSN Varchar2(40),
  constraint pk_ScientificJournal primary key (Journal_ID)
  constraint fk_Publisher_id foreign key (Publisher_id) references Publishers(Publisher_id)
);

CREATE TABLE Academic(
  AcademicID varchar2(40),
  AcademicName varchar2(40),
  emailaddress varchar2(40),
  Researchfields varchar2(40),
  Phone Number(10,0),
  constraint pk_Academic_ID PRIMARY KEY (Academic_ID),
  CONSTRAINT FK_Academic_ID
    FOREIGN KEY (Academic_ID)
      REFERENCES Organisations(Academic_ID)
);

CREATE TABLE Reviews(
  Date varchar2(40),
  ReviewerID varchar2(40),
  ReviewID Varchar2(40),
  PaperID Varchar2(40),
  ReviewsResult varchar2(40),
  PRIMARY KEY (ReviewID),
  CONSTRAINT FK_ReviewerID
    FOREIGN KEY (ReviewerID)
      REFERENCES Academic(AcademicID)
);

CREATE TABLE Organisations(
  OrganisationID  Varchar2(40),
  O_Name  varchar2(40),
  Address  varchar2(40),
  Phonenumber number(10,0),
  OrganisationType Varchar2(40),
  ResearchFields Varchar2(40),
  Subscriptions varchar2(40),
  Associates Varchar2(40),
  PRIMARY KEY (OrganisationID),
  CONSTRAINT FK_O_Name
    FOREIGN KEY (O_name)
      REFERENCES Reviews(Result)
);

CREATE TABLE Paper(
  PaperID Varchar2(40),
  Title varchar2(40),
  DateSubmission date,
  Dateaccepted Date,
  DateWritten date,
  Subject Area varchar2(40),
  Corresponding author Varchar2(40),
  Co-author Varchar2(40),
  Journal_ID Varchar2(40),
  IssueNo number(10,0),
  constraint pk_PaperID PRIMARY KEY (PaperID),
      REFERENCES ScientificJournal(ISSN),
  CONSTRAINT FK_Corresponding author
    FOREIGN KEY (Corresponding author)
      REFERENCES Academic(emailaddress),
  CONSTRAINT FK_PaperID
    FOREIGN KEY (PaperID)
      REFERENCES Reviews(PaperID)
);

Errors:

Error starting at line : 12 in command -

CREATE TABLE ScientificJournal( Journal_ID Varchar2(40), JournalName Type, Issues Type, Volumes Number(10,0), Editor Varchar2(40), Publisher_id Varchar2(40), ISSN Varchar2(40), constraint pk_ScientificJournal primary key (Journal_ID) constraint fk_Publisher_id foreign key (Publisher_id) references Publishers(Publisher_id) )

Error report -
ORA-00907: missing right parenthesis
00907. 00000 - "missing right parenthesis"

Error starting at line : 24 in command -

CREATE TABLE Academic( AcademicID varchar2(40), AcademicName varchar2(40), emailaddress varchar2(40), Researchfields varchar2(40), Phone Number(10,0), constraint pk_Academic_ID PRIMARY KEY (Academic_ID), CONSTRAINT FK_Academic_ID FOREIGN KEY (Academic_ID) REFERENCES Organisations(Academic_ID) )
Error report -
ORA-00904: "ACADEMIC_ID": invalid identifier
00904. 00000 - "%s: invalid identifier"

Error starting at line : 36 in command -
CREATE TABLE Reviews( Date varchar2(40), ReviewerID varchar2(40), ReviewID Varchar2(40), PaperID Varchar2(40), ReviewsResult varchar2(40), PRIMARY KEY (ReviewID), CONSTRAINT FK_ReviewerID FOREIGN KEY (ReviewerID) REFERENCES Academic(AcademicID) )
Error report
ORA-00904: : invalid identifier
00904. 00000 - "%s: invalid identifier"

Error starting at line : 48 in command -
CREATE TABLE Organisations( OrganisationID Varchar2(40), O_Name varchar2(40), Address varchar2(40), Phonenumber number(10,0), OrganisationType Varchar2(40), ResearchFields Varchar2(40), Subscriptions varchar2(40), Associates Varchar2(40), PRIMARY KEY (OrganisationID), CONSTRAINT FK_O_Name FOREIGN KEY (O_name) REFERENCES Reviews(Result) )

Error report -
ORA-00942: table or view does not exist
00942. 00000 - "table or view does not exist"

Error starting at line : 63 in command -
CREATE TABLE Paper( PaperID Varchar2(40), Title varchar2(40), DateSubmission date, Dateaccepted Date, DateWritten date, Subject Area varchar2(40), Corresponding author Varchar2(40), Co-author Varchar2(40), Journal_ID Varchar2(40), IssueNo number(10,0), constraint pk_PaperID PRIMARY KEY (PaperID), REFERENCES ScientificJournal(ISSN), CONSTRAINT FK_Corresponding author FOREIGN KEY (Corresponding author) REFERENCES Academic(emailaddress), CONSTRAINT FK_PaperID FOREIGN KEY (PaperID) REFERENCES Reviews(PaperID) )

Error report -
ORA-00907: missing right parenthesis
00907. 00000 - "missing right parenthesis"

CodePudding user response：

Quite a few errors.

scientificjournal: missing comma between CONSTRAINTs, TYPE is invalid datatype
academic: references organisations, so organisations has to be created BEFORE academic
organisations references reviews, so reviews has to be created before organisations
reviews: DATE is invalid column name - it is reserved for datatype. It references table academic, but - it can't be created (see above). Use ALTER TABLE to enforce referential integrity constraints.
paper: minus sign or space not allowed in column name. You can't reference columns that aren't primary/unique keys

When fixed (sort of; you'll have to review it, though):

SQL> CREATE TABLE publishers (
  2      publisher_id   VARCHAR2(40),
  3      publisher_name VARCHAR2(40),
  4      countries      VARCHAR2(40),
  5      addresses      VARCHAR2(40),
  6      phone          NUMBER(10, 0),
  7      emails         VARCHAR2(40),
  8      journals       VARCHAR2(40),
  9      CONSTRAINT pk_publisher_id PRIMARY KEY ( publisher_id )
 10  );

Table created.

SQL>
SQL> CREATE TABLE scientificjournal (
  2      journal_id   VARCHAR2(40),
  3      journalname  VARCHAR2(10),
  4      issues       VARCHAR2(10),
  5      volumes      NUMBER(10, 0),
  6      editor       VARCHAR2(40),
  7      publisher_id VARCHAR2(40),
  8      issn         VARCHAR2(40),
  9      CONSTRAINT pk_scientificjournal PRIMARY KEY ( journal_id )
 10  );

Table created.

SQL>
SQL> CREATE TABLE reviews (
  2      cdate         VARCHAR2(40),
  3      reviewerid    VARCHAR2(40),
  4      reviewid      VARCHAR2(40),
  5      paperid       VARCHAR2(40),
  6      reviewsresult VARCHAR2(40),
  7      PRIMARY KEY ( reviewid )
  8  );

Table created.

SQL>
SQL> CREATE TABLE academic (
  2      academicid     VARCHAR2(40),
  3      academicname   VARCHAR2(40),
  4      emailaddress   VARCHAR2(40),
  5      researchfields VARCHAR2(40),
  6      phone          NUMBER(10, 0),
  7      CONSTRAINT pk_academic_id PRIMARY KEY ( academicid )
  8  );

Table created.

SQL>
SQL> CREATE TABLE organisations (
  2      organisationid   VARCHAR2(40),
  3      o_name           VARCHAR2(40),
  4      address          VARCHAR2(40),
  5      phonenumber      NUMBER(10, 0),
  6      organisationtype VARCHAR2(40),
  7      researchfields   VARCHAR2(40),
  8      subscriptions    VARCHAR2(40),
  9      associates       VARCHAR2(40),
 10      PRIMARY KEY ( organisationid )
 11  );

Table created.

SQL>
SQL> CREATE TABLE paper (
  2      paperid              VARCHAR2(40),
  3      title                VARCHAR2(40),
  4      datesubmission       DATE,
  5      dateaccepted         DATE,
  6      datewritten          DATE,
  7      subject_area         VARCHAR2(40),
  8      corresponding_author VARCHAR2(40),
  9      co_author            VARCHAR2(40),
 10      journal_id           VARCHAR2(40),
 11      issueno              NUMBER(10, 0),
 12      CONSTRAINT pk_paperid PRIMARY KEY ( paperid )
 13  );

Table created.

SQL>
SQL> ALTER TABLE scientificjournal
  2      ADD CONSTRAINT fk_publisher_id FOREIGN KEY ( publisher_id )
  3          REFERENCES publishers ( publisher_id );

Table altered.

SQL>
SQL> ALTER TABLE reviews
  2      ADD CONSTRAINT fk_reviewerid FOREIGN KEY ( reviewerid )
  3          REFERENCES academic ( academicid );

Table altered.

SQL>
SQL> ALTER TABLE organisations
  2      ADD CONSTRAINT fk_o_name FOREIGN KEY ( o_name )
  3          REFERENCES reviews ( reviewid );

Table altered.

SQL>
SQL> ALTER TABLE academic
  2      ADD CONSTRAINT fk_academic_id FOREIGN KEY ( academicid )
  3          REFERENCES organisations ( organisationid );

Table altered.

SQL>
SQL> ALTER TABLE paper
  2      ADD CONSTRAINT fk_corresponding_author FOREIGN KEY ( corresponding_author )
  3          REFERENCES academic ( academicid );

Table altered.

SQL>
SQL> ALTER TABLE paper
  2      ADD CONSTRAINT fk_paperid FOREIGN KEY ( paperid )
  3          REFERENCES reviews ( reviewid );

Table altered.

SQL>
SQL> ALTER TABLE paper
  2      ADD CONSTRAINT fk_scientific FOREIGN KEY ( journal_id )
  3          REFERENCES scientificjournal ( journal_id );

Table altered.

SQL>

CodePudding user response：

If you run your 2nd SQL it errors out because of the following columns, I dont think there is any type datatype in Oracle.

  JournalName Type,
  Issues Type,



CREATE TABLE ScientificJournal(
  Journal_ID Varchar2(40),
  JournalName Type,
  Issues Type,
  Volumes Number(10,0),
  Editor Varchar2(40),
  Publisher_id Varchar2(40),
  ISSN Varchar2(40),
  constraint pk_ScientificJournal primary key (Journal_ID)
  constraint fk_Publisher_id foreign key (Publisher_id) references Publishers(Publisher_id)
);