Oracle创建Borrow表触发ORA-00905错误，求问题原因解析

首先来看你遇到的报错信息和建表语句：

报错信息 #

Error starting at line : 72 in command - create table Borrow( bookID int, memberID int, dayOfBorrowed date not null, dayToReturn date not null, giverUser int not null, CONSTRAINT check_dayToReturn check(dayToReturn>dayOfBorrowed), primary key (bookID, memberID), foreign key (bookID) references Books(bookID) on delete cascade, foreign key (memberID) references Members(memberID) on delete cascade, foreign key (giverUser) references Users(userID) on update no action on delete no action)
Error report - ORA-00905: missing keyword
00905. 00000 - "missing keyword"
*Cause:
*Action:

问题根源 #

核心问题出在Oracle数据库不支持外键约束中显式指定ON UPDATE NO ACTION。

Oracle对于外键的更新行为，默认规则就是NO ACTION（意思是如果Users表的userID被关联到Borrow表的giverUser，那么不允许修改这个userID，除非先删除Borrow表中的关联记录），而且Oracle的语法不允许你显式写出这个默认规则，一旦写了就会触发"missing keyword"的语法错误。

修正后的建表语句 #

只需要去掉on update no action部分即可，因为默认行为已经符合你的需求：

create table Borrow( 
    bookID int, 
    memberID int, 
    dayOfBorrowed date not null, 
    dayToReturn date not null, 
    giverUser int not null, 
    CONSTRAINT check_dayToReturn check(dayToReturn>dayOfBorrowed), 
    primary key (bookID, memberID), 
    foreign key (bookID) references Books(bookID) on delete cascade, 
    foreign key (memberID) references Members(memberID) on delete cascade, 
    foreign key (giverUser) references Users(userID) on delete no action
);

如果你的实际需求是希望当Users表的userID更新时，Borrow表的giverUser也同步更新，那可以把on update no action替换为on update cascade，但如果不需要这个同步更新逻辑，直接去掉该语句即可。

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