2

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

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

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.

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!

OscuroAA
  • 252
  • 2
  • 17
  • 1
    It sounds like it's not allowed - http://stackoverflow.com/questions/1289877/how-to-create-a-foreign-key-with-on-update-cascade-on-oracle – Aidan Kane Jan 19 '14 at 01:24
  • The on delete null on update cascade? Do you have any idea how come? For the record (I'll add it to the post), I do have a Hotel table as well. Which creates just fine.. -- My bad. I didn't follow your link. Thank you! Appreciate your help :) – OscuroAA Jan 19 '14 at 02:52

1 Answers1

1

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)

Check this link

San
  • 4,508
  • 1
  • 13
  • 19