0

I'm beginer and I'm working on oracle 12c database so, In my database project I want to apply cascade on delete and on update simultaneously as i did in mysql but when i apply tha same technique in oracle it show me the error so how can i do that?

MImranKhan
  • 27
  • 7

1 Answers1

1

There is no ON UPDATE CASCADE on Oracle. While you can probably argue updating a table's primary key is valid in SQL, you probably should not, hence the decision of Oracle not to implement it.

More info here:
https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:5773459616034

EDIT: As discussed in comments below, think of that constraint as a way Oracle prevents people from doing something wrong (updating primary keys).
The correct way to handle the case of a primary key that might be updated is to create a separate field that will act as the surrogate primary key. The surrogate key, of course, is immutable.
The danger of using a natural key as primary key is discussed there.

FXD
  • 1,960
  • 1
  • 6
  • 9
  • Updating PK - valid or not it is possible with [deferred constraints](https://stackoverflow.com/questions/18271622/generate-sql-to-update-primary-key/49265508#49265508). I did similar thing on Postgresql. [Oracle Deferred Constraints](https://oracle-base.com/articles/8i/constraint-checking-updates#Deferred) – Lukasz Szozda Dec 27 '18 at 16:34
  • Deferred constraints is a possibility mentioned in my link indeed. BTW, I am also a postgresql user an although it allows to have `ON UPDATE CASCADE`, I would never do it (I agree with Tom's point that a primary key should never be updated). – FXD Dec 27 '18 at 16:47
  • "Should" or "could" are two different things. It's better to have an option and never use it :) – Lukasz Szozda Dec 27 '18 at 16:48
  • As long as it is under control I would say ... I like the idea of **not** having the option at all if it prevents me/coworkers from doing mistakes. If from the start you work with the idea a primary key is to never be updated, you will avoid preparing your downfall in the future. Check out the answer of https://stackoverflow.com/questions/337503/whats-the-best-practice-for-primary-keys-in-tables (especially starting point 3 + down to the comments). – FXD Dec 27 '18 at 16:56
  • Fair enough. :) – Lukasz Szozda Dec 27 '18 at 17:00