CODEWITHSUNDEEP
sqloracle-databaseoracle10gconstraints
Fverswijver
Fverswijver

Reputation: 465

Create constraint alter table invalid

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?

Upvotes: 1

Views: 1840

Answers (2)

CloudyMarble
CloudyMarble

Reputation: 37566

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.

Upvotes: 0

Tommi
Tommi

Reputation: 8608

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?

Upvotes: 2

Related Questions