“缺少右括号":在删除集上为空在更新级联(SQL/Oracle)上 [英] "Missing right parenthesis": On Delete Set Null On Update Cascade (SQL/Oracle)

问题描述

我遇到(另一个?)缺少右括号"错误.

I'm having (another?) 'Missing right parenthesis' error.

我对SQL完全陌生，所以我真的不知道问题可能出在哪里.

I'm completely new to SQL, so I don't really have much idea what the problem could be.

实际上似乎没有缺少括号.但是，我不断收到在更新级联上的ON DELETE SET NULL上"行引发的错误.

It doesn't appear to be, actually, a missing parenthesis. But I continually get the error thrown on the "ON DELETE SET NULL ON UPDATE CASCADE" line.

我非常感谢您确定问题所在的任何帮助.

I'd really appreciate any help ascertaining what the problem is.

我正在使用Oracle 11g Express运行我的.sql文件.

I'm using Oracle 11g Express to run my .sql file.

CREATE TABLE Hotel
(hotelNo          CHAR(10)          NOT NULL
,hotelName        VARCHAR2(50)      NOT NULL
,city             VARCHAR2(50)      NOT NULL
,CONSTRAINT PKHotel PRIMARY KEY (hotelNo)
);
--
CREATE TABLE Room
(roomNo           CHAR(3)           NOT NULL
,hotelNo          CHAR(10)          NOT NULL
,roomType         VARCHAR2(20)      NOT NULL
,price            DECIMAL(6,2)      NOT NULL
,CONSTRAINT checkRoomNo CHECK (roomNo BETWEEN 1 AND 100)
,CONSTRAINT checkType CHECK (roomType IN ('Single', 'Double', 'Family'))
,CONSTRAINT checkPrice CHECK (price BETWEEN 10.00 AND 100.00)
,CONSTRAINT PKRoom PRIMARY KEY (roomNo, hotelNo)
,CONSTRAINT FKHotel FOREIGN KEY (hotelNo) REFERENCES Hotel(hotelNo)
    ON DELETE SET NULL ON UPDATE CASCADE
);
--

非常感谢.我真的很感激！

Thanks a lot. I really appreciate it!

推荐答案

在Oracle中无法进行级联更新.汤姆·凯特(Tom Kite)说:

On Update Cascade is not possible in Oracle. Tom Kite says :

There is not "on update cascade" automagically.

There are ways to do it, 

o deferrable constraints.  defer the foreign key check until commit, update the parent, 
update the child and then commit.

Personally -- I've never found a need or use for update cascade.  I'm opposed to it.  If 
your design requires it -- change your design now if you can.  


Primary keys are supposed to be imutable, never changing, constant.  It is an excessively 
bad practice to have to update them ever.  If there is a 0.00001% chance you will have to 
update a primary key -- then it is not a primary key, its a surrogate key and you need to 
find the true primary key (even if you have to make it up via a sequence) 

选中此链接

这篇关于“缺少右括号":在删除集上为空在更新级联(SQL/Oracle)上的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！