2

I'm trying to perform the following statement into oracle:

alter table COMENTARIO
add constraint FK_COMENTARIO_DI foreign key (DI_ID)
  references DATO_DE_INTERES (DI_ID) ON UPDATE CASCADE ON DELETE SET NULL;

However, I get ORA-00905 missing keyword.

When I remove the ON UPDATE statement though, the command works without any problem. Why is this? Is there any options in case I can't use ON UPDATE? Thank you beforehand!

OMG Ponies
  • 325,700
  • 82
  • 523
  • 502
Carlos
  • 21
  • 2

3 Answers3

2

There is no "ON UPDATE" clause for cascading constraints. I don't know of any alternatives aside from some sort of application enforcement or triggers.

DCookie
  • 42,630
  • 11
  • 83
  • 92
  • Which means there is no ON UPDATE CASCADE, right? What options do I have? And how come one of my classmates is working with oracle too and had no problems using that sentence? – Carlos Dec 29 '10 at 21:56
  • Your classmate must not have used exactly that statement. There is no ON UPDATE clause so ON UPDATE CASCADE is invalid syntax. – Justin Cave Dec 29 '10 at 22:03
  • So, no on update clause in oracle? That would explain why I can't use it no matter what option I use... right? – Carlos Dec 29 '10 at 22:07
  • Correct. There is no ON UPDATE clause in Oracle. A foreign key constraint cannot automatically process updates to the primary key of the parent table. – Justin Cave Dec 29 '10 at 22:16
  • Oh, thank you. Then, I will just remove the ON UPDATE clauses and everything should be okay. Just out of curiosity, what DB engines do support this? – Carlos Dec 29 '10 at 22:22
  • I believe MySQL and PostgreSQL both support ON UPDATE CASCADE. I imagine there are other databases that do as well though I would generally advise against data models that rely on that sort of functionality. – Justin Cave Dec 29 '10 at 23:08
  • Thanks!!! Fine then, I believe I can start doing the user layers, functions and triggers... – Carlos Dec 29 '10 at 23:15
1

There is no such option as ON UPDATE CASCADE in Oracle. Maybe instead of looking for a way to implement this (I think it is possible with ON UPDATE trigger) you could tell why do you need this. I mean - why would you want to update the primary key of DATO_DE_INTERES?

Marcin Wroblewski
  • 3,491
  • 18
  • 27
  • Which is a good question. I'm mostly doing this "in case of", instead of really doing it for a reason. – Carlos Dec 29 '10 at 22:01
  • OK, "in case of" you can look here: http://tkyte.blogspot.com/2009/10/httpasktomoraclecomtkyteupdatecascade.html – Marcin Wroblewski Dec 29 '10 at 22:09
  • Thank you! But, since it's more of a DDL thing, I gotta explain my teacher why I can't (and shouldn't) use ON UPDATE... – Carlos Dec 29 '10 at 22:13
1

In Oracle, there is no ON UPDATE clause in a constraint definition. In the vast majority of cases, you wouldn't want to implement this "just in case" because primary keys should be immutable. If your primary keys are not immutable, that would generally be indicative of a data model problem that should be addressed rather than coded around.

That said, if you really want to implement something like that, Tom Kyte does have an update cascade package. But you're far better off designing the system to avoid the problem in the first place rather than designing in this level of complexity.

Justin Cave
  • 227,342
  • 24
  • 367
  • 384