ORA-00907缺失右括号错误：Oracle外键声明问题求助

Hey there! I see you've spent hours troubleshooting this Oracle Live SQL issue—let's get this sorted out quickly.

The root cause of your ORA-00907: missing right parenthesis error is straightforward: Oracle does NOT support the ON UPDATE CASCADE clause in foreign key constraints. Unlike some other databases (like MySQL), Oracle only allows ON DELETE actions for foreign keys; ON UPDATE isn't a valid option here, which is throwing off the syntax parser and making it look like you're missing a parenthesis.

Corrected Enrollment Table Code #

Here's the fixed version of your Enrollment table creation statement—all I've done is remove the invalid ON UPDATE CASCADE clauses:

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,
    CONSTRAINT StudentFK FOREIGN KEY (StdNo) REFERENCES Student ON DELETE CASCADE
);

Quick Explanation #

• Oracle doesn't support automatic cascading updates for foreign keys. If you later need to update a primary key (like OfferNo or StdNo) and have those changes propagate to the Enrollment table, you'd need to create a database trigger to handle that logic. But for your current assignment, removing the ON UPDATE CASCADE clauses will resolve the syntax error and let you create the table successfully.

Full Working SQL Code (for reference) #

If you want to run the entire script with the fix, here's the complete version:

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,
    CONSTRAINT StudentFK FOREIGN KEY (StdNo) REFERENCES Student ON DELETE CASCADE
);

内容的提问来源于stack exchange，提问作者Steve