CODEWITHSUNDEEP
oracle-databaseoracle-sqldeveloper
RG.A
RG.A

Reputation: 57

Creating Tables and seeing this error - ORA-00907: missing right parenthesis

CREATE TABLE Office(
       Office_ID INT NOT NULL GENERATED BY DEFAULT AS IDENTITY,
       Phone_Number CHAR(11) NOT NULL,
            CONSTRAINT Office_PK PRIMARY KEY (Office_ID));
CREATE TABLE Faculty(
       Faculty_ID INT NOT NULL GENERATED BY DEFAULT AS IDENTITY,
       First_Name VARCHAR(25) NOT NULL,
       Last_Name VARCHAR (25) NOT NULL,
       Office_ID INT NOT NULL,
            CONSTRAINT Faculty_PK PRIMARY KEY (Faculty_ID) 
            CONSTRAINT Faculty_FK FOREIGN KEY (Office_ID) REFERENCES Office(Office_ID));
CREATE TABLE Student(
       Student_ID INT NOT NULL GENERATED BY DEFAULT AS IDENTITY,
       First_Name VARCHAR(25) NOT NULL,
       Last_Name VARCHAR(25) NOT NULL,
       Phone_Number CHAR(11) NOT NULL,
       Sposue_Name VARCHAR (30),
       Street_Address VARCHAR(30) NOT NULL,
       City VARCHAR(20) NOT NULL,
       State CHAR(2) NOT NULL,
       Zip VARCHAR(9) NOT NULL,
       Advisor_ID INT NOT NULL,
            CONSTRAINT Student_PK PRIMARY KEY (Student_ID) 
            CONSTRAINT Student_FK FOREIGN KEY (Advisor_ID) REFERENCES Faculty(Faculty_ID));

Can someone help me understand what I'm missing? I don't understand where the error is coming from.

Upvotes: 0

Views: 38

Answers (1)

Littlefoot
Littlefoot

Reputation: 143163

It is not e.g.

Office_ID INT NOT NULL GENERATED BY DEFAULT AS IDENTITY,

but

Office_ID INT GENERATED BY DEFAULT AS IDENTITY NOT NULL,

(not null comes last).

Also, you miss comma at the end of CONSTRAINT statements in the last two tables.

Once you fix that, it works OK.

SQL> CREATE TABLE Office(
  2         Office_ID INT GENERATED BY DEFAULT AS IDENTITY NOT NULL ,
  3         Phone_Number CHAR(11) NOT NULL,
  4              CONSTRAINT Office_PK PRIMARY KEY (Office_ID));

Table created.

SQL> CREATE TABLE Faculty(
  2         Faculty_ID INT GENERATED BY DEFAULT AS IDENTITY NOT NULL,
  3         First_Name VARCHAR(25) NOT NULL,
  4         Last_Name VARCHAR (25) NOT NULL,
  5         Office_ID INT NOT NULL,
  6              CONSTRAINT Faculty_PK PRIMARY KEY (Faculty_ID),  --> missing comma
  7              CONSTRAINT Faculty_FK FOREIGN KEY (Office_ID) REFERENCES Office(Office_ID));

Table created.

SQL> CREATE TABLE Student(
  2         Student_ID INT GENERATED BY DEFAULT AS IDENTITY NOT NULL ,
  3         First_Name VARCHAR(25) NOT NULL,
  4         Last_Name VARCHAR(25) NOT NULL,
  5         Phone_Number CHAR(11) NOT NULL,
  6         Sposue_Name VARCHAR (30),
  7         Street_Address VARCHAR(30) NOT NULL,
  8         City VARCHAR(20) NOT NULL,
  9         State CHAR(2) NOT NULL,
 10         Zip VARCHAR(9) NOT NULL,
 11         Advisor_ID INT NOT NULL,
 12              CONSTRAINT Student_PK PRIMARY KEY (Student_ID),   --> missing comma
 13              CONSTRAINT Student_FK FOREIGN KEY (Advisor_ID) REFERENCES Faculty(Faculty_ID));

Table created.

SQL>

Upvotes: 1

Related Questions