在Oracle中添加ON UPDATE CASCADE与ON DELETE CASCADE时遇ORA-00905错误求助

错误根源是Oracle不支持外键约束中的ON UPDATE CASCADE选项，Oracle的外键语法仅提供ON DELETE相关的级联操作（如ON DELETE CASCADE、ON DELETE SET NULL等），没有ON UPDATE CASCADE的语法支持，这才触发了"缺失关键字"的报错。

修正后的建表语句如下：

create table Register(
    stdno char(5),
    courseId char(8),
    SemesterId char(5),
    mark decimal(4,2) check (mark between 0.00 and 100.00),

    constraint fK_student foreign key (stdno) references student (stdno) on delete cascade,
    constraint fK_course foreign key (courseId) references course (courseId) on delete cascade,
    constraint fK_Semester foreign key (SemesterId) references Semester (SemesterId) on delete cascade,
    constraint pK_Register primary key (stdno, courseid, SemesterId)
);

如果需要实现类似ON UPDATE CASCADE的级联更新效果，Oracle中只能通过创建触发器来实现。例如，当student表的stdno更新时，同步更新Register表的对应字段：

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;
/

同理，针对course和Semester表的主键更新，也需要创建类似的触发器。

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