CODEWITHSUNDEEP
sqldatabaseoracle-databaseora-00905
Carlos
Carlos

Reputation: 21

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!

Upvotes: 2

Views: 1635

Answers (3)

Justin Cave
Justin Cave

Reputation: 231661

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.

Upvotes: 1

Marcin Wroblewski
Marcin Wroblewski

Reputation: 3571

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?

Upvotes: 1

DCookie
DCookie

Reputation: 43523

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.

Upvotes: 2

Related Questions