CODEWITHSUNDEEP
sqloracle-database
Steve
Steve

Reputation: 193

"ORA-00907: missing right parenthesis" in foreign key declaration

I am a university student who are new to Oracle Live SQL. My professor give us one page of code and let us debugging.

Only 1 error in the last paragraph(Enrollment TABLE) left. All tables except Enrollment table have been successfully created. I spent 6 hours on last paragraph but still can not find the wrong place. I have checked all the information on the web and did not find solution.

Please help, thanks!

CREATE TABLE Faculty ( 
FacNo         CHAR(11)      NOT NULL, 
FacFirstName  VARCHAR(30)   NOT NULL, 
FacLastName   VARCHAR(30)   NOT NULL, 
FacCity       VARCHAR(30)   NOT NULL, 
FacState      CHAR(2)       NOT NULL, 
FacDept       CHAR(6)       NULL, 
FacRank       CHAR(4)       NULL, 
FacSalary     DECIMAL(10,2) NULL, 
FacSupervisor CHAR(11)      NULL, 
FacHireDate   DATE      NULL, 
FacZipCode    CHAR(10)      NOT NULL, 
CONSTRAINT FacultyPK PRIMARY KEY (FacNo),  
CONSTRAINT SupervisorFK FOREIGN KEY (FacSupervisor) REFERENCES Faculty);

CREATE TABLE Course ( 
CourseNo      CHAR(6)       NOT NULL, 
CrsDesc       VARCHAR(50)   NOT NULL, 
CrsUnits      INTEGER       NULL, 
CONSTRAINT CoursePK PRIMARY KEY (CourseNo) );

CREATE TABLE Offering ( 
OfferNo       INTEGER       NOT NULL, 
CourseNo      CHAR(6)       NOT NULL, 
OffTerm       CHAR(6)       NOT NULL, 
OffYear       INTEGER       NOT NULL, 
OffLocation   VARCHAR(30)   NULL, 
OffTime       VARCHAR(10)   NULL, 
FacNo         CHAR(11)      NULL, 
OffDays       CHAR(4)       NULL, 
CONSTRAINT OfferingPK PRIMARY KEY (OfferNo), 
CONSTRAINT CourseFK FOREIGN KEY (CourseNo) REFERENCES Course, 
CONSTRAINT FacultyFK FOREIGN KEY (FacNo) REFERENCES Faculty);

CREATE TABLE Student ( 
StdNo         CHAR(11)      NOT NULL, 
StdFirstName  VARCHAR(30)   NOT NULL, 
StdLastName   VARCHAR(30)   NOT NULL, 
StdCity       VARCHAR(30)   NOT NULL, 
StdState      CHAR(2)       NOT NULL, 
StdZip        CHAR(10)      NOT NULL, 
StdMajor      CHAR(6)       NULL, 
StdClass      CHAR(2)       NULL, 
StdGPA        DECIMAL(3,2)  NULL, 
CONSTRAINT StudentPk PRIMARY KEY (StdNo) ) 

CREATE TABLE Enrollment( 
OfferNo       INTEGER       NOT NULL, 
StdNo         CHAR(11)      NOT NULL, 
EnrGrade      DECIMAL(3,2)  NULL, 
CONSTRAINT EnrollmentPK PRIMARY KEY (OfferNo,StdNo), 
CONSTRAINT OfferingFK FOREIGN KEY (OfferNo) REFERENCES Offering 
                      ON DELETE CASCADE
                      ON UPDATE CASCADE, 
CONSTRAINT StudentFK FOREIGN KEY (StdNo) REFERENCES Student 
                      ON DELETE CASCADE 
                      ON UPDATE CASCADE);

Upvotes: 1

Views: 391

Answers (1)

GMB
GMB

Reputation: 222462

First of all, there is missing semicolon at the end of the CREATE TABLE Student statement.

But I believe that what your teacher wants to emphasize is that ON UPDATE is not supported by Oracle foreign keys. As far as concerns, Oracle's point of view is that primary keys are meant to be immutable.

If you comment this part of the statement, the code runs fine :

CREATE TABLE Enrollment( 
OfferNo       INTEGER       NOT NULL, 
StdNo         CHAR(11)      NOT NULL, 
EnrGrade      DECIMAL(3,2)  NULL, 
CONSTRAINT EnrollmentPK PRIMARY KEY (OfferNo,StdNo), 
CONSTRAINT OfferingFK FOREIGN KEY (OfferNo) REFERENCES Offering 
                      ON DELETE CASCADE,
                      -- ON UPDATE CASCADE, 
CONSTRAINT StudentFK FOREIGN KEY (StdNo) REFERENCES Student 
                      ON DELETE CASCADE 
                      -- ON UPDATE CASCADE
);

Demo on DB Fiddle.

Upvotes: 1

Related Questions