CODEWITHSUNDEEP
sqloracle-database
hetptis
hetptis

Reputation: 806

ON UPDATE RESTRICT ON DELETE CASCADE

ALTER TABLE IDN_APPMGT_CLAIM_MAPPING 
ADD CONSTRAINT CLAIMID_APPID_CONSTRAINT FOREIGN KEY (APP_ID) REFERENCES IDN_APPMGT_APP (ID) 
ON UPDATE RESTRICT
ON DELETE CASCADE;

I want to translate this SQL to Oracle. Since Oracle does not have ON UPDATE RESTRICT I cannot imaging how this should be done. Will just omitting the ON UPDATE clause solve the problem?

ALTER TABLE IDN_APPMGT_CLAIM_MAPPING 
ADD CONSTRAINT CLAIMID_APPID_CONSTRAINT FOREIGN KEY (APP_ID) REFERENCES IDN_APPMGT_APP (ID) 
ON DELETE CASCADE;

Upvotes: 4

Views: 12291

Answers (1)

Luke Woodward
Luke Woodward

Reputation: 64949

According to https://dev.mysql.com/doc/refman/5.6/en/create-table-foreign-keys.html, RESTRICT in an ON UPDATE or ON DELETE clause:

Rejects the delete or update operation for the parent table.

In other words, attempting to change a value in the parent table when rows in a child table reference this value will fail.

You can't specify an ON UPDATE clause in an Oracle foreign-key constraint, as Oracle doesn't support ON UPDATE ... options in foreign-key constraints. However, that's not a problem for you as Oracle's behaviour with UPDATE statements and foreign-key constraints is the same as MySQL's behaviour with ON UPDATE RESTRICT. In fact, ON UPDATE RESTRICT is the default in MySQL as well, so you could have just omitted it in both databases.

So, in summary, yes, omitting the ON UPDATE RESTRICT clause from the constraint will solve your problem.

Upvotes: 6

Related Questions