Create constraint alter table invalid

Question

I had to modify an existing constraint so it would cascade updates and deletes. To do this I first removed the constraint and was planning on adding it (through an ALTER TABLE) but this fails.

When I commit the query below it gives me the error 'ORA-01735: invalid ALTER TABLE option':

ALTER TABLE 
    PARAM 
ADD CONSTRAINT 
    FK_PARAM_PORTLET FOREIGN KEY (PORTLETID) 
REFERENCES PORTLET(ID) 
ON DELETE CASCADE ON UPDATE CASCADE;

Any idea what it could be? Am I overlooking something?

score 2 · Accepted Answer · edited May 23 '17 at 12:13

2

Oracle does not support ON UPDATE CASCADE in foreign keys.

Have a look at this question for tips: How to create a Foreign Key with "ON UPDATE CASCADE" on Oracle?

edited May 23 '17 at 12:13

Community

1
1

answered Apr 13 '11 at 08:45

Tommi

8,550
5
32
51

CloudyMarble · Answer 2 · 2013-03-14T15:00:40.117

UPDATE CASCADE is not supported in Oracle. You will need to manage this via triggers.

Check Oracle statement:

Referential integrity constraints can specify particular actions to be performed on the dependent rows in a child table if a referenced parent key value is modified. The referential actions supported by the FOREIGN KEY integrity constraints of Oracle are UPDATE and DELETE NO ACTION, and DELETE CASCADE.

Create constraint alter table invalid

2 Answers2