Getting ORA-00905 error when trying to add a foreign key
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!
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.
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?
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.