"Missing Right Parenthesis": on Delete Set Null on Update Cascade (Sql/Oracle)

Missing right parenthesis : On Delete Set Null On Update Cascade (SQL/Oracle)

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

ORA-00907: missing right parenthesis in foreign key declaration

First of all, there is missing semicolon at the end of the CREATE TABLE Student statement.

But I believe that what your teacher wants to emphasize is that ON UPDATE is not supported by Oracle foreign keys. As far as concerns, Oracle's point of view is that primary keys are meant to be immutable.

If you comment this part of the statement, the code runs fine :

CREATE TABLE Enrollment( 
OfferNo       INTEGER       NOT NULL, 
StdNo         CHAR(11)      NOT NULL, 
EnrGrade      DECIMAL(3,2)  NULL, 
CONSTRAINT EnrollmentPK PRIMARY KEY (OfferNo,StdNo), 
CONSTRAINT OfferingFK FOREIGN KEY (OfferNo) REFERENCES Offering 
                      ON DELETE CASCADE,
                      -- ON UPDATE CASCADE, 
CONSTRAINT StudentFK FOREIGN KEY (StdNo) REFERENCES Student 
                      ON DELETE CASCADE 
                      -- ON UPDATE CASCADE
);

Demo on DB Fiddle.

ORA missing right parenthesis and unimplemented feature

Oracle doesn't support the following features:

ON UPDATE CASCADE

ON DELETE SET DEFAULT

Presumably, your script will run fine if you remove them. Personally, I've never needed either of them (since I always use surrogate primary keys that are never changed).

Getting missing keyword error when using ON DELETE/UPDATE in CREATE TABLE

The answer is simple - in Oracle, there's no ON UPDATE clause so - remove it. You can leave ON DELETE (if it does something; if it is NO ACTION, you can omit it).

ORA-00907: missing right parenthesis and ORA-00942: table or view does not exist

A foreign key definition is wrong here:

CREATE TABLE first_aider(
    ......
    ......
    FOREIGN KEY(staffId) REFERENCES staff(staffId) ON DELETE CASCADE ON UPDATE CASCADE
);

Oracle doesn't know ON UPDATE CASCADE clause, according to the documentation only ON DELETE CASCADE or ON DELETE SET NULL are allowed. You must remove ON UPDATE CASCADE.

---

The same error is here:

CREATE TABLE security(
    ......
    ......
    FOREIGN KEY(staffId) REFERENCES staff(staffId) ON DELETE CASCADE ON UPDATE CASCADE
);

---

and also here:

CREATE TABLE supervisor(
    ......
    ......
    FOREIGN KEY(staffId) REFERENCES staff(staffId) ON DELETE CASCADE ON UPDATE CASCADE
);

why i have an error when i use on update cascade?

there is no ON UPDATE CASCADE in oracle.

you may want to look at deferrable constraints which defers the foreign key check until commit, update the parent, update the child and then commit.

ALTER TABLE affaire_cassation
ADD CONSTRAINT key_fk_num_p 
FOREIGN KEY(num_aff_a) 
REFERENCES affaire_appel(num_aff)
DEFERRABLE INITIALLY DEFERRED;

You may also look for DEFERRABLE INITIALLY IMMEDIATE where you can defer the constraints on demand when you need it.

---

use with ALTER SESSION SET CONSTRAINTS = DEFERRED;

---

Related Topics