创建表并通过外键关联时提示缺失关键字问题排查

报错的核心原因是Oracle数据库不支持外键约束中的ON UPDATE CASCADE语法，你在Register表的第一个外键定义里使用了这个Oracle不认可的子句，导致触发“缺失关键字”错误。

直接移除ON UPDATE CASCADE即可让SQL正常执行，修正后的完整代码如下：

CREATE TABLE Student (
    stdNo CHAR(5) PRIMARY KEY,
    lastname VARCHAR2(25) NOT NULL,
    givennames VARCHAR2(50) NOT NULL,
    dept CHAR(4)
);
CREATE TABLE Course (
  courseID CHAR(8) PRIMARY KEY,
  courseTitle VARCHAR2(50) UNIQUE NOT NULL,
  credits INT DEFAULT 2 CHECK (credits >= 0 AND credits <= 200)
);
CREATE TABLE Semester (
  semesterID CHAR(5) PRIMARY KEY,
  semesterCode INT CHECK (semesterCode BETWEEN 1 AND 4),
  semesterYear INT CHECK (semesterYear BETWEEN 2000 AND 9999)
);
CREATE TABLE Register (
  stdNo CHAR(5),
  courseID CHAR(8),
  semesterID CHAR(5),
  grade CHAR(2),
  mark DECIMAL(4, 2),
  PRIMARY KEY (stdNo, courseID, semesterID),
  FOREIGN KEY (stdNo) REFERENCES Student(stdNo) ON DELETE CASCADE,
  FOREIGN KEY (courseID) REFERENCES Course(courseID),
  FOREIGN KEY (semesterID) REFERENCES Semester(semesterID),
  CHECK (mark >= 0.00 AND mark <= 100.00)
);

如果你的业务逻辑需要实现“更新Student表的stdNo时，自动同步更新Register表中的对应记录”，Oracle无法通过外键直接实现，需要手动创建触发器来完成这个逻辑，示例代码如下：

CREATE OR REPLACE TRIGGER trg_update_stdno
AFTER UPDATE OF stdNo ON Student
FOR EACH ROW
BEGIN
  UPDATE Register
  SET stdNo = :NEW.stdNo
  WHERE stdNo = :OLD.stdNo;
END;
/

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